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]

We need to know what columns these are in your image.

Are they column A to D?

And where exactly do you plan to enter the criterial for the search and where exactly will the result be entered.

And in your image
The same pair of gloves were shown in two different rows with different dates with the same emp number but same price so was not sure why you used this example.

 

[jmjeichavez]

Yes they are column A to D..and i plan on using data validation to select the criteria employee ID in (B1) and Product in (B2) and have the amount auto populate in (B3). As for the example, even though the amount and the product is the same i need to have it select the latest amount based on date for both criteria even if its the same from a prior sale. Reason being, the amount can change depending on what the employees sales the gloves at.

 

[My Aswer Is This]

So since we have:

employee ID in (B1) and Product in (B2) and have the amount auto populate in (B3).

So we must start the search in row (4) I assume. Is that correct.
Or will we start in row 6

 

[jmjeichavez]

start in row 6

 

[My Aswer Is This]

This may be more difficult then I'm able to help with.
The problem is determining which date and time are the latest.

I can find employee number and product but then if there are many that meet that then I have to compare maybe many dates and times to see which one is latest.

Maybe someone else on this forum will have a answer.

I will try but hopefully someone else here will have a answer.

 

[Fluff]

How about

Sub findLatest()
   Dim LastSale As Date
   Dim Rng As Range
   Dim Cl As Range
   
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      Set Rng = .Range("A6", .Range("A" & Rows.Count).End(xlUp))
      
      With .Range("A5:D5")
         .AutoFilter 2, Range("B1").Value
         .AutoFilter 3, Range("B2").Value
      End With
      LastSale = WorksheetFunction.Subtotal(4, Rng)

      For Each Cl In Rng.SpecialCells(xlVisible)
         If Cl.Value = LastSale Then .Range("B3").Value = Cl.Offset(, 3).Value
      Next Cl
      .AutoFilterMode = False
   End With

End Sub

 

[My Aswer Is This]

Looks like that should work. I was attemting to compare dates and times against Now()
Your answer will work assuming all entry's are in listed in order by date.
Which it appears to be.

How about

Sub findLatest()
   Dim LastSale As Date
   Dim Rng As Range
   Dim Cl As Range
   
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      Set Rng = .Range("A6", .Range("A" & Rows.Count).End(xlUp))
      
      With .Range("A5:D5")
         .AutoFilter 2, Range("B1").Value
         .AutoFilter 3, Range("B2").Value
      End With
      LastSale = WorksheetFunction.Subtotal(4, Rng)

      For Each Cl In Rng.SpecialCells(xlVisible)
         If Cl.Value = LastSale Then .Range("B3").Value = Cl.Offset(, 3).Value
      Next Cl
      .AutoFilterMode = False
   End With

End Sub

 

[Fluff]

The order shouldn't matter as this line

LastSale = WorksheetFunction.Subtotal(4, Rng)

Should fine the highest number in the visible cells.
It's the same as the worksheet MAX function, but works on filtered data

 

[Fluff]

Another option, getting rid of the loop

Sub findLatest()
   Dim LastSale As Date
   Dim Rng As Range
   Dim Cl As Range
   
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      Set Rng = .Range("A6", .Range("A" & Rows.Count).End(xlUp))
      
      With .Range("A5:D5")
         .AutoFilter 2, Range("B1").Value
         .AutoFilter 3, Range("B2").Value
      End With
      LastSale = WorksheetFunction.Subtotal(4, Rng)
      Set Cl = Rng.Find(LastSale)
      If Not Cl Is Nothing Then
         Range("B3").Value = Cl.Offset(, 3).Value
      Else
         MsgBox "Nothing found"
      End If
      .AutoFilterMode = False
   End With

End Sub