PDA

View Full Version : VBA Filter problem



Kiel
11-17-2015, 10:55 AM
Hi,

I have a column that contains dates in long date format, for instance: "Tuesday, November 17, 2015".
Also new dates are added everyday.
Each Friday I want macro to show me (to automatically filter) results for next Monday. So let's say this Friday I want it to filter for Monday, Nov. 23.
Firstly I have a filter for next week and then I want to filter again with criteria that is the word "Monday".

I wrote this code but it does not work, nothing is showing, only blank column with filter on.




Dim Datef As String
Datef = "Monday"
ActiveSheet.Range("F:F").AutoFilter Field:=1, Criteria1:=xlFilterNextWeek, Operator:=xlAnd, Criteria2:="=*Datef*", Operator:=xFilterValues
DropDown = True


Could you help me with this issue?

p45cal
11-18-2015, 12:43 PM
forget the Datef and such and try:

lDate = CLng(Date + 8 - Weekday(Date, 2))
ActiveSheet.Range("F:F").AutoFilter Field:=1, Criteria1:=">=" & lDate, Operator:=xlAnd, Criteria2:="<" & lDate + 1
Dates can be very problematic, especially if they're not using a standard formatting on your sheet.

ref: look at the section headed FILTER BY EXACT DATE here: http://www.ozgrid.com/VBA/autofilter-vba-dates.htm

Kiel
11-20-2015, 08:54 AM
Thank you, I'll check this out