Auto Filter - VBA Code doesn't work with Dates!

Posted by JAF on February 28, 2001 7:52 AM

Hiya

This is a very weird situation that has got me completely baffled.

I have a spreadsheet with 3 columns headed Date, Division and Value (Cells A1, B1 and C1 respectively).

The Date column contains dates from 01/01/2001 to 31/01/2001 (Note UK date format of dd/mm/yyyy). The Division column contains either Alpha, Beta or Gamma. The Value column contains a currency formatted amount.

I recorded 3 seperate macros to get the VBA code for the Auto Filter which produced the following:
Sub Filter_Date()
Selection.AutoFilter Field:=1, Criteria1:="24/01/2001"
End Sub
Sub Filter_Division()
Selection.AutoFilter Field:=2, Criteria1:="Gamma"
End Sub
Sub Filter_Value()
Selection.AutoFilter Field:=3, Criteria1:="£75.44"
End Sub

The Filter_Division and Filter_Value code works without any problem, but the Filter_Date code returns no records, even though there is a record with the date 24th January.

Why is Excel doing this and is there any way to make it behave itself and get it run the Filter_Date code correctly??

JAF

Posted by Mark W. on February 28, 2001 8:31 AM

You're using a text string rather than a date value.
Gotta remember those data types.

Posted by Mark W. on February 28, 2001 8:57 AM

Gonna have to eat some crow! The use of the text
string probably isn't your problem. It is working
for me; however, you need to keep one thing in mind
your text string must conform to the formatting of
your date data.

Posted by David Hawley on February 28, 2001 6:52 PM

JAF, When using dates in VBA it makes good practice to use the DateSerial function:

Dateserial(Year,Month,Day) This will account for international issues.

So in your case pass your date to a String
variable like below

Dim Mydate As String
Mydate = DateSerial(2000, 4, 7)

And use "MyDate" as your date.

Dave
OzGrid Business Applications