VBA to filter most recent date from filtered entries

[Tanyaann1995]

Hi,

I'm using the below code to filter out a series of data. First, the customer name has to be filtered, then the Item has to be filtered and then the most recent date in Column CA has to be found. I want the code to find the most recent date from the filtered entries in that column but the result shows the most recent date in the entire column. Please can you advise how to fix this. I have attached sample of how the sheet looks like.

Dim i As Integer
Dim lRow As Long
Dim pno As String
Dim name As String
Dim max_date As Date
Dim pfind As Range
Dim m As Long
Dim n As String

lRow = ThisWorkbook.Worksheets(2).Range("E:E").Find(what:="*", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

name = InputBox("Input customer name to search against")

For i = 24 To lRow
If IsEmpty(Cells(i, 4).Value) = False Then
pno = ThisWorkbook.Worksheets(2).Cells(i, 4).Value
With Workbooks("Latest hist prices 3.xlsx").Worksheets(1)
.Range("A1:DV25290").AutoFilter Field:=67, Criteria1:="=*" & pno & "*"
.Range("A1:DV25290").AutoFilter Field:=15, Criteria1:="=*" & name & "*"
max_date = Application.WorksheetFunction.Max(.Columns("CA"))
Set pfind = .Range("CA:CA").Find(what:=max_date, LookIn:=xlValues, LookAt:=xlWhole)
m = pfind.Offset(0, -27).Value
n = pfind.Offset(0, -28).Value
ThisWorkbook.Worksheets(2).Cells(i, 21).Value = m & n & "-" & max_date
End With
End If
Next i

End Sub

 

[Domenic]

Try using the Subtotal method of the WorksheetFunction object instead to return the maximum from a filtered list...

max_date = Application.WorksheetFunction.Subtotal(4, .Columns("CA"))

Hope this helps!

 

[Tanyaann1995]

Try using the Subtotal method of the WorksheetFunction object instead to return the maximum from a filtered list...

max_date = Application.WorksheetFunction.Subtotal(4, .Columns("CA"))

Hope this helps!

Thanks, the above code works for finding the maximum in a filtered range. But, there is a run time error 91 appearing after the below line in code:

m = pfind.Offset(0, -27).Value

I'm not able to figure out why this error keeps popping up. Please help.

 

[Domenic]

That's because .Range("CA:CA").Find(...) didn't find a match, and returned the keyword Nothing. And, as a result, pfind is assigned Nothing. This is probably happening because no records are found when the data is filtered, and so max_date is assign the value 0. So you can test as follows...

    If Not pfind Is Nothing Then
        'do stuff
    End If

Although, instead of relying on the Find method to determine whether any records are found, I would suggest that you filter the data, and then immediately test whether any records are found. If so, then proceed to do the necessary work. Otherwise, on to the next iteration.