VBA autofilter bug??
 

Can anyone help me explain why the following code does not work. It appears
to be a bug in Excel 2003.

This is a simple spreadsheet that contains a command button that calls a
small piece of vba code to do an autofilter on a range of dates.

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1/01/2003", Operator:=xlAnd,
Criteria2:="<31/03/2003"

When the line is executed, it returns no data, however if you manually go
into the autofilter drop down the values are there and a simple OK to the
dialogue box will return the correct results.

The spreadsheet contains the following

Record Number Date
1 2/01/2003
2 1/02/2003
3 1/03/2003
4 1/04/2003
5 1/04/2003
6 1/05/2003


The code above should return record numbers 1 thru 3, but it displays no
lines of code util you go in manually


Am I missing something?

VBA autofilter bug??
 

try

Criteria1:="1/1/2003", Operator:=xlAnd,
Criteria2:="<3/31/2003"


I have found that, even with my set to UK dates
(dd/mm/yy) that the filter needs US staye dates (
mm/dd/yy)


Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Can anyone help me explain why the following code does
not work. It appears
to be a bug in Excel 2003.

This is a simple spreadsheet that contains a command
button that calls a
small piece of vba code to do an autofilter on a range
of dates.

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2,
Criteria1:="1/01/2003", Operator:=xlAnd,
Criteria2:="<31/03/2003"

When the line is executed, it returns no data, however
if you manually go
into the autofilter drop down the values are there and a
simple OK to the
dialogue box will return the correct results.

The spreadsheet contains the following

Record Number Date
1 2/01/2003
2 1/02/2003
3 1/03/2003
4 1/04/2003
5 1/04/2003
6 1/05/2003


The code above should return record numbers 1 thru 3,
but it displays no
lines of code util you go in manually


Am I missing something?





.

VBA autofilter bug??
 

Selection.AutoFilter Field:=2, Criteria1:="" & DateSerial(2003,1,1),
Operator:=xlAnd,
Criteria2:="<" & DateSerial(2003,3,31)

Excel VBA generally works in US date formats. The interface between the
autofilter criteria and VBA, when dealing with dates, is tenuous, but
usually using dateserials will work.

--
Regards,
Tom Ogilvy

"godgo" <godgo@godgo wrote in message
u...
Can anyone help me explain why the following code does not work. It
appears
to be a bug in Excel 2003.

This is a simple spreadsheet that contains a command button that calls a
small piece of vba code to do an autofilter on a range of dates.

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1/01/2003",
Operator:=xlAnd,
Criteria2:="<31/03/2003"

When the line is executed, it returns no data, however if you manually go
into the autofilter drop down the values are there and a simple OK to the
dialogue box will return the correct results.

The spreadsheet contains the following

Record Number Date
1 2/01/2003
2 1/02/2003
3 1/03/2003
4 1/04/2003
5 1/04/2003
6 1/05/2003


The code above should return record numbers 1 thru 3, but it displays no
lines of code util you go in manually


Am I missing something?

VBA autofilter bug??
 

Thanks Patrick. That did the trick.

regards
dion

"Patrick Molloy" wrote in message
...
try

Criteria1:="1/1/2003", Operator:=xlAnd,
Criteria2:="<3/31/2003"


I have found that, even with my set to UK dates
(dd/mm/yy) that the filter needs US staye dates (
mm/dd/yy)


Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Can anyone help me explain why the following code does
not work. It appears
to be a bug in Excel 2003.

This is a simple spreadsheet that contains a command
button that calls a
small piece of vba code to do an autofilter on a range
of dates.

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2,
Criteria1:="1/01/2003", Operator:=xlAnd,
Criteria2:="<31/03/2003"

When the line is executed, it returns no data, however
if you manually go
into the autofilter drop down the values are there and a
simple OK to the
dialogue box will return the correct results.

The spreadsheet contains the following

Record Number Date
1 2/01/2003
2 1/02/2003
3 1/03/2003
4 1/04/2003
5 1/04/2003
6 1/05/2003


The code above should return record numbers 1 thru 3,
but it displays no
lines of code util you go in manually


Am I missing something?





.

VBA autofilter bug??
 

godgo emailed me that this didn't work.

It works fine for me and I have others in regions with dd/mm/yyyy format
tell me it works for them.

If it didn't work for him/her I suspect there is some reason related to the
particulars of the data in their sheet.

--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
Selection.AutoFilter Field:=2, Criteria1:="" & DateSerial(2003,1,1),
Operator:=xlAnd,
Criteria2:="<" & DateSerial(2003,3,31)

Excel VBA generally works in US date formats. The interface between the
autofilter criteria and VBA, when dealing with dates, is tenuous, but
usually using dateserials will work.

--
Regards,
Tom Ogilvy

"godgo" <godgo@godgo wrote in message
u...
Can anyone help me explain why the following code does not work. It
appears
to be a bug in Excel 2003.

This is a simple spreadsheet that contains a command button that calls a
small piece of vba code to do an autofilter on a range of dates.

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1/01/2003",
Operator:=xlAnd,
Criteria2:="<31/03/2003"

When the line is executed, it returns no data, however if you manually
go
into the autofilter drop down the values are there and a simple OK to
the
dialogue box will return the correct results.

The spreadsheet contains the following

Record Number Date
1 2/01/2003
2 1/02/2003
3 1/03/2003
4 1/04/2003
5 1/04/2003
6 1/05/2003


The code above should return record numbers 1 thru 3, but it displays no
lines of code util you go in manually


Am I missing something?

VBA autofilter bug??
 

Try it:

.AutoFilter Field:=3, Criteria1:="=" & Format(Date, "mm/dd/yy") :

--
VEspanh
-----------------------------------------------------------------------
VEspanha's Profile: http://www.excelforum.com/member.php...fo&userid=2522
View this thread: http://www.excelforum.com/showthread.php?threadid=18248

VBA autofilter bug??
 

I can only see this reply, so I am guessing at the probem, but I know a
probem in Autofilter with dates.

Try this

AutoFilter Field:=3, Criteria1:="=" & Format(Date, "mm/dd/yy"), _
Operator:=xlAnd, _
Criteria1:="<=" & Format(Date, "mm/dd/yy")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VEspanha" wrote in
message ...

Try it:

AutoFilter Field:=3, Criteria1:="=" & Format(Date, "mm/dd/yy") :)


--
VEspanha
------------------------------------------------------------------------
VEspanha's Profile:
http://www.excelforum.com/member.php...o&userid=25223
View this thread: http://www.excelforum.com/showthread...hreadid=182482