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!

EmployeeID	jc8277
Product	Gloves
Amount	$ 15.00
Updated	EmpID	Product	Amount
9/11/2017 13:08	st4345	Shoes	$ 89.00
9/11/2017 13:11	jc8277	Gloves	$ 15.00
9/11/2017 13:11	jc8277	Scarf	$ 10.00
10/2/2017 12:54	bb8899	Shoes	$ 90.00
10/2/2017 13:16	st4345	Gloves	$ 15.00
10/2/2017 13:16	st4345	Shoes	$ 100.00
10/24/2017 14:34	jc8277	Shoes	$ 79.00
10/24/2017 14:34	jc8277	Gloves	$ 15.00
11/3/2017 10:22	bb8899	Scarf	$ 10.00
11/3/2017 10:22	bb8899	Gloves	$ 15.00

<tbody>
</tbody>

 

[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