Date flips around when using Autofilter in VB
 

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.

Date flips around when using Autofilter in VB
 

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.

--
Regards,
Tom Ogilvy


"elsandalos" wrote in message
...
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.

Date flips around when using Autofilter in VB
 

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" wrote:

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.

--
Regards,
Tom Ogilvy


"elsandalos" wrote in message
...
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.

Date flips around when using Autofilter in VB
 

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.

--
Regards,
Tom Ogilvy


"elsandalos" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy


"elsandalos" wrote in message
...
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.

Date flips around when using Autofilter in VB
 

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"elsandalos" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy


"elsandalos" wrote in message
...
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.