VBA to filter most recent date from filtered entries

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Capture13.PNG
    Capture13.PNG
    44.2 KB · Views: 10

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,753
Try using the Subtotal method of the WorksheetFunction object instead to return the maximum from a filtered list...

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


Hope this helps!
 
Solution

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
Try using the Subtotal method of the WorksheetFunction object instead to return the maximum from a filtered list...

VBA Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,753
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...

VBA Code:
    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.
 

Forum statistics

Threads
1,143,907
Messages
5,721,436
Members
422,362
Latest member
elliotpat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top