Find row number based on on mulitple criteria

[jmjeichavez]

Hello,

I am looking for a VBA solution that can find the latest data entry using 2 criteria. For instances, looking at the table below, if i were to enter the employee ID and the product type it would return $79. I am trying to avoid formulas to accomplish this task. Any help would be much much appreciated!

[TABLE="width: 400"]
<tbody>[TR]
[TD]EmployeeID
[/TD]
[TD="align: right"]jc8277[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Product
[/TD]
[TD="align: right"]Gloves[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Amount
[/TD]
[TD="align: right"] $ 15.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Updated
[/TD]
[TD="align: center"]EmpID
[/TD]
[TD="align: center"]Product
[/TD]
[TD="align: center"]Amount
[/TD]
[/TR]
[TR]
[TD="align: center"]9/11/2017 13:08[/TD]
[TD="align: center"]st4345[/TD]
[TD="align: center"]Shoes[/TD]
[TD="align: center"] $ 89.00[/TD]
[/TR]
[TR]
[TD="align: center"]9/11/2017 13:11[/TD]
[TD="align: center"]jc8277[/TD]
[TD="align: center"]Gloves[/TD]
[TD="align: center"] $ 15.00[/TD]
[/TR]
[TR]
[TD="align: center"]9/11/2017 13:11[/TD]
[TD="align: center"]jc8277[/TD]
[TD="align: center"]Scarf[/TD]
[TD="align: center"] $ 10.00[/TD]
[/TR]
[TR]
[TD="align: center"]10/2/2017 12:54[/TD]
[TD="align: center"]bb8899[/TD]
[TD="align: center"]Shoes[/TD]
[TD="align: center"] $ 90.00[/TD]
[/TR]
[TR]
[TD="align: center"]10/2/2017 13:16[/TD]
[TD="align: center"]st4345[/TD]
[TD="align: center"]Gloves[/TD]
[TD="align: center"] $ 15.00[/TD]
[/TR]
[TR]
[TD="align: center"]10/2/2017 13:16[/TD]
[TD="align: center"]st4345[/TD]
[TD="align: center"]Shoes[/TD]
[TD="align: center"] $ 100.00[/TD]
[/TR]
[TR]
[TD="align: center"]10/24/2017 14:34[/TD]
[TD="align: center"]jc8277[/TD]
[TD="align: center"]Shoes[/TD]
[TD="align: center"] $ 79.00[/TD]
[/TR]
[TR]
[TD="align: center"]10/24/2017 14:34[/TD]
[TD="align: center"]jc8277[/TD]
[TD="align: center"]Gloves[/TD]
[TD="align: center"] $ 15.00[/TD]
[/TR]
[TR]
[TD="align: center"]11/3/2017 10:22[/TD]
[TD="align: center"]bb8899[/TD]
[TD="align: center"]Scarf[/TD]
[TD="align: center"] $ 10.00[/TD]
[/TR]
[TR]
[TD="align: center"]11/3/2017 10:22[/TD]
[TD="align: center"]bb8899[/TD]
[TD="align: center"]Gloves[/TD]
[TD="align: center"] $ 15.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

This script will work if the dates are in order

I'm not sure how to use "Max" with something like this. When not using filter
Not sure why filter is needed.

Sub Check_Products_New()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim EmployeeID As String
Dim Product As String
Dim x As Long
x = 1
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
EmployeeID = Range("B1").Value
Product = Range("B2").Value
    For i = 6 To Lastrow
        If Cells(i, 2).Value = EmployeeID Then
            If Cells(i, 3).Value = Product Then
                x = i
        End If
        End If
         Next
Range("B3").Value = Cells(x, 4).Value
Application.ScreenUpdating = True
End Sub