excel - When Cells match with certain columns in a table, do X

Question

Because I'm a big noob at VBA I can't seem to get this problem solved. I have the following problem:

I have an "Import" sheet where I import data. On that sheet I have 3 cells that change value all the time, C3,C4 and C5.

When these cells match with the cells from another table on another sheet I want to manipulate the value from a different column within the same table.

On an image it will look something like this:

When Column A matches with Apples, and Column B matches with Oranges, and Column C matches with Peaches then I want to do something to the number on the same row of column D.

I wrote the following code but it isn't working and just returning all the numbers from column D. I would I don't know what's going wrong.

I hope someone here could help me out.

Dim c1 As Range
Dim c2 As Range
Dim c3 As Range
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim a1 As Range
Dim a2 As Range
Dim a3 As Range

Set r1 = Sheets("Specificaties").Range("H" & Rows.Count).End(xlUp)
Set r2 = Sheets("Specificaties").Range("I" & Rows.Count).End(xlUp)
Set r3 = Sheets("Specificaties").Range("J" & Rows.Count).End(xlUp)


Sleutel = Sheets("Specificaties").Range("M" & Rows.Count).End(xlUp).Row

Set a1 = Sheets("Import").Range("C3")
Set a2 = Sheets("Import").Range("C4")
Set a3 = Sheets("Import").Range("C5")

    For Each c1 In r1
            For Each c2 In r2
                    For Each c3 In r3
                    If c3.Value = a3.Value And _
                        c2.Value = a2.Value And _
                        c1.Value = a1.Value Then
                    
                         For i = Sleutel To 3 Step -1
                         MsgBox Cells(i, 13).Value
                         Next
                         
                    Else
                    MsgBox "Negative result"
                    End If
    Next
    Next
    Next

End Sub

Answer 1

I agree with @SJR that Autofilter would be a good choice, however this is an option. This assumes that your column labels of A-D are in columns H-K based on your code or numerically 8-11 and that your data starts in row 4. I only chose one of the columns for the length since there must be a value in all 3 columns in order to meet your described criteria. Your 3 nested loops would not check each row in order.

Sub test()

    Dim r1 As Long
    Dim x As Long
    
    Dim a1 As Range
    Dim a2 As Range
    Dim a3 As Range
    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    
    Set wks1 = ThisWorkbook.Sheets("Specificates")
    Set wks2 = ThisWorkbook.Sheets("Import")
    
    Set r1 = wks1.Range("H" & Rows.Count).End(xlUp).Row
    
    Set a1 = wks2.Range("C3")
    Set a2 = wks2.Range("C4")
    Set a3 = wks2.Range("C5")
    
    For x = 4 To r1
        If wks1.Cells(x, 8).Value = a1.Value And wks1.Cells(x, 9).Value = a2.Value And wks1.Cells(x, 10).Value = a3.Value Then
            MsgBox wks1.Cells(i, 11).Value
        Else
            MsgBox "Negative Result"
        End If
    Next x
    
End Sub