Date flips around when using Autofilter in VB

[Guest]

I'm using VB to autofilter a number of worksheets using a criteria entered in
a textbox.

--------------------------------------------------------------------------------
Sub Filter_Box3()

Dim ws As Worksheet
Dim crit As String
Dim agsheetname As String

crit = Sheet1.Filtertext2.Value

Application.ScreenUpdating = False

For Each ws In Worksheets
agsheetname = ws.Name
If agsheetname <> "Guide" Then

ws.UsedRange.AutoFilter Field:=1, Criteria1:="<" & crit

End If

Next ws

Application.ScreenUpdating = True

End Sub

--------------------------------------------------------------------------------

for some reason, when using < or > the date has been flipped around when i
check the autofilter settings on the worksheets, so that if crit =
01/02/2005, it gets changed to 02/01/2005. This does not happen when using =
in the criteria. Even when i hard code a date into the function like:

-------------------------------------------------------------------------------
ws.UsedRange.AutoFilter Field:=1, Criteria1:="<01/02/2005"

--------------------------------------------------------------------------------

.... it still gets flipped around.

Any ideas?

El.

 

[Tom Ogilvy]

ws.UsedRange.AutoFilter Field:=1, Criteria1:="<" & _
format(cdate("01/02/2005"),"mm/dd/yyyyy")

I would bet you have the problem even with "=" if the month and day can be
flipped and still form a good date.

In VBA, you need to work with US formatted dates if you are going to work
with strings, otherwise, work with date serial numbers.

 

[Guest]

Cheers Tom.

Oddly i don't get the problem when using "=", even if the month and day can
be flipped. I've just read around, and seen that people are using cdbl when
feeding a date into the autofilter. I've got it fixed with that, and also
using your method.

still strikes me as odd though. Whats the difference between feeding
autofilter a criteria through VB and doing it manually on the worksheet?

Anyway - ta for the help

El.

 

[Tom Ogilvy]

cdbl used with a date string such as

cdbl("01/23/2005")

won't work. so you must not be sending it a date string.

anyway, the difference is that one is done with VBA and one is done with
Excel. They are two different products and have different behaviors.

 

[Guest]

yeah, sorry - i changed the variable from a string to date.