GB Allan


Hi all,

I have 2 similar macros to write - one is to custom filter in a column all dates less than or equal to today's date. The other macro is to to filter in a column all dates greater than today's date, but less than or equal to 2 calendar weeks from today's date. So if today is 7/3/07, the first would filter all dates up to and including 7/3/07, the second would give me all dates 7/4/07 up to and including 7/17/07.

I wrote the following for the first one:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/3/2007 by GB
'

Dim MyDate
MyDate = Date
Range("B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="<=MyDate", Operator:=xlAnd
End Sub

But it doesn't work - it appears to be treating MyDate is a text string instead of today's date. I tried taking the quote marks out, but I'm getting an error message.

I have a feeling that if I get the first one resolve, I should be able to figure out the second. Thank you,

Gary




Re: Macro for Custom Filter - Today's Date

magicalclick


Hello, try this.

Selection.AutoFilter Field:=2, Criteria1:="<=" & cstr(MyDate), Operator:=xlAnd

The key is you put the date value in the criteria rather than the name of the variable. And you need to turn the Data object to String value and added to the criteria string.

Two basics:

1) you can add strings togather, like "Hello " + "World" or "Hello " & "World", or HelloStringObject + " " + WorldStringObject.

2) data type importance. Even though you may not need this knowledge, but strong data type is recommanded practice. Each object has a data type associated with it, like Date, Interger (123), Double (12.32), String (ABC), and customer data type that stores like pictures. You need to convert data type before combining them.

Happy Coding.






Re: Macro for Custom Filter - Today's Date

GB Allan

Thanks so much - it works perfectly. And thanks for the code lesson!

Gary