Dave Hawley - VBA Date Filter - Still Not Working

Posted by JAF on March 01, 2001 1:55 AM

Dave

Thanks for your suggestion as to a solution for my VBA Date Filter problem, but I've tried what you suggested and Excel is still not running the filter correctly.

The code I'm using is:
Sub Filter_Date()
Dim MyDate As String
MyDate = DateSerial(2001, 1, 24)
Selection.AutoFilter Field:=1, Criteria1:=MyDate
End Sub

... which should return entries which have a date of 24/01/2001, is still returning no records, even though there are entries in the column of that value.

Suggestions??

JAF

Posted by David Hawley on March 01, 2001 2:56 AM

Hi JAF

Using Dates with Autofilter in VBA is a real pain. The example I gave before will only work if you are looking for a date between it and another, sorry. When you need to find an exact date you MUST format it exactly how it is formatted in the cell. So if you dates are formatted like: "dd/mm/yy" this will work.

Sub Filter_Date()
Dim MyDate As String
MyDate = Format(DateSerial(2001, 1, 24), "dd/mm/yy")
ActiveSheet.AutoFilterMode = False
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=MyDate
End Sub

Dave

OzGrid Business Applications

Posted by Mark W. on March 01, 2001 7:23 AM

Dave, are you pinching from MY web site...

11126.html

Posted by JAF on March 01, 2001 8:32 AM

Dave/Mark - Success (sort of)

Guys

Thanks for your suggestions for the solution to my problem. I tried your suggestions but was still coming up with the same problem (even using the formatted DateSerial code).

I managed to fluke a solution by formatting my cells to be dd mmm yyyy instead of dd/mm/yyyy and this has resolved it.

While not an ideal solution, it works, so what the hell!!

I can only assume that this is a UK problem given the way we write our dates - Excel obviously doesn't cater for dates in a "foreign" format when running AutoFilter VBA code - go figure!.

JAF

Posted by Mark W. on March 01, 2001 9:30 AM

Re: Dave/Mark - Success (sort of)

I'm not so sure about your conclusion. Once I
confirmed that the format of a text string
representation of a date must match the actual
date format, your original code (shown below)
worked just fine for me!

Sub Filter_Date()
Selection.AutoFilter Field:=1, Criteria1:="24/01/2001"
End Sub

Given all that has transpired why don't you revert
back to your original code, format your dates as
dd/mm/yyyy and give it another go!

Posted by David Hawley on March 01, 2001 4:04 PM

JAF, It does work. Look.

Jaf

You must be doing something wrong as we in Australia also use d/m/y rather than m/d/y

Put a heading of some sort in cell A1.
Put some any dates in cells A2:A10 (ensure at least one is "24/01/01")
Format cells A2:A10 as "dd/mm/yy". Use the "Custom" format to be sure.
Run the code below:

Any good ?
OzGrid Business Applications

Posted by JAF on March 02, 2001 1:12 AM

Re: JAF, It does work. Look.

David

Thanks again for your input on this.

The code you have put below does work - HOWEVER, if the dates are formatted to dd/mm/yyyy then it doesn't work.

I've got a couple of alternatives to use now (either formatting as dd mmm yyyy or dd/mm/yy, both of which do work)

JAF Field:=1, Criteria1:=MyDate