Hi,

Always I face a problem when i try to filter a range based on date. If
I do it manually it works fine. But when I try to do this thru code it
dont work.

May be there is a better way to do this?

when I recoded my action i get:

Sheets("Detail").select
Range("A1").AutoFilter Field:=1, Criteria1:="=01/07/2006",
Operator:=xlAnd _
, Criteria2:="<=31/07/2006"

and when i run this code again no records are shown !!!

Damn US dates <g

Dim date1_value As Date
Dim date2_value As Date

Sheets("Detail").Select
date1_value = DateValue("01-Jul-2006")
date2_value = DateValue("31-Jul-2006")
Range("A1").AutoFilter Field:=1, _
Criteria1:="=" & Str(CDbl(date1_value)), _
Operator:=xlAnd, _
Criteria2:="<=" & Str(CDbl(date2_value))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Abdul" wrote in message
oups.com...
Hi,

Always I face a problem when i try to filter a range based on date. If
I do it manually it works fine. But when I try to do this thru code it
dont work.

May be there is a better way to do this?

when I recoded my action i get:

Sheets("Detail").select
Range("A1").AutoFilter Field:=1, Criteria1:="=01/07/2006",
Operator:=xlAnd _
, Criteria2:="<=31/07/2006"

and when i run this code again no records are shown !!!

Just wondering, is it necessary to do all that or simply

Range("A1").AutoFilter Field:=1, _
Criteria1:="=07/Jul/2006", _
Operator:=xlAnd _
, Criteria2:="<=31/Aug/2006"

as long as the month is spelt and in US date order

Regards,
Peter T

"Bob Phillips" wrote in message
...
Damn US dates <g

Dim date1_value As Date
Dim date2_value As Date

Sheets("Detail").Select
date1_value = DateValue("01-Jul-2006")
date2_value = DateValue("31-Jul-2006")
Range("A1").AutoFilter Field:=1, _
Criteria1:="=" & Str(CDbl(date1_value)), _
Operator:=xlAnd, _
Criteria2:="<=" & Str(CDbl(date2_value))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Abdul" wrote in message
oups.com...
Hi,

Always I face a problem when i try to filter a range based on date. If
I do it manually it works fine. But when I try to do this thru code it
dont work.

May be there is a better way to do this?

when I recoded my action i get:

Sheets("Detail").select
Range("A1").AutoFilter Field:=1, Criteria1:="=01/07/2006",
Operator:=xlAnd _
, Criteria2:="<=31/07/2006"

and when i run this code again no records are shown !!!

Hi Peter,

My suggestion was more of a generic solution. It should work wherever you
get the date from, a cell, a userform etc., and filter correctly. You have
effectively shortcut my solution for those particular dates.

Regards

Bob

"Peter T" <peter_t@discussions wrote in message
...
Just wondering, is it necessary to do all that or simply

Range("A1").AutoFilter Field:=1, _
Criteria1:="=07/Jul/2006", _
Operator:=xlAnd _
, Criteria2:="<=31/Aug/2006"

as long as the month is spelt and in US date order

Regards,
Peter T

"Bob Phillips" wrote in message
...
Damn US dates <g

Dim date1_value As Date
Dim date2_value As Date

Sheets("Detail").Select
date1_value = DateValue("01-Jul-2006")
date2_value = DateValue("31-Jul-2006")
Range("A1").AutoFilter Field:=1, _
Criteria1:="=" & Str(CDbl(date1_value)), _
Operator:=xlAnd, _
Criteria2:="<=" & Str(CDbl(date2_value))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Abdul" wrote in message
oups.com...
Hi,

Always I face a problem when i try to filter a range based on date. If
I do it manually it works fine. But when I try to do this thru code it
dont work.

May be there is a better way to do this?

when I recoded my action i get:

Sheets("Detail").select
Range("A1").AutoFilter Field:=1, Criteria1:="=01/07/2006",
Operator:=xlAnd _
, Criteria2:="<=31/07/2006"

and when i run this code again no records are shown !!!

Hi Bob,

I have always found that providing a date string is un-ambiguous it
correctly converts. Not sure about the "-" as date separator in all systems
(but "/" is always OK), also not sure if "July" is recognized in all
languages. But I see your solution would be better practice, I'll try not
to be lazy <g

Regards,
Peter T

"Bob Phillips" wrote in message
...
Hi Peter,

My suggestion was more of a generic solution. It should work wherever you
get the date from, a cell, a userform etc., and filter correctly. You have
effectively shortcut my solution for those particular dates.

Regards

Bob

"Peter T" <peter_t@discussions wrote in message
...
Just wondering, is it necessary to do all that or simply

Range("A1").AutoFilter Field:=1, _
Criteria1:="=07/Jul/2006", _
Operator:=xlAnd _
, Criteria2:="<=31/Aug/2006"

as long as the month is spelt and in US date order

Regards,
Peter T

"Bob Phillips" wrote in message
...
Damn US dates <g

Dim date1_value As Date
Dim date2_value As Date

Sheets("Detail").Select
date1_value = DateValue("01-Jul-2006")
date2_value = DateValue("31-Jul-2006")
Range("A1").AutoFilter Field:=1, _
Criteria1:="=" & Str(CDbl(date1_value)), _
Operator:=xlAnd, _
Criteria2:="<=" & Str(CDbl(date2_value))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Abdul" wrote in message
oups.com...
Hi,

Always I face a problem when i try to filter a range based on date.
If
I do it manually it works fine. But when I try to do this thru code
it
dont work.

May be there is a better way to do this?

when I recoded my action i get:

Sheets("Detail").select
Range("A1").AutoFilter Field:=1, Criteria1:="=01/07/2006",
Operator:=xlAnd _
, Criteria2:="<=31/07/2006"

and when i run this code again no records are shown !!!

Peter,

I absolutely agree about unambiguous dates. When I use is SUMPRODUCT
solutions I try to use an ISO standard yyyy-mm-dd, I haven't yet heard from
anyone that "-" is a problem. And certainly July would not be recognised in
Excel with our friends across the channel.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,

I have always found that providing a date string is un-ambiguous it
correctly converts. Not sure about the "-" as date separator in all
systems
(but "/" is always OK), also not sure if "July" is recognized in all
languages. But I see your solution would be better practice, I'll try not
to be lazy <g

Regards,
Peter T

"Bob Phillips" wrote in message
...
Hi Peter,

My suggestion was more of a generic solution. It should work wherever
you
get the date from, a cell, a userform etc., and filter correctly. You
have
effectively shortcut my solution for those particular dates.

Regards

Bob

"Peter T" <peter_t@discussions wrote in message
...
Just wondering, is it necessary to do all that or simply

Range("A1").AutoFilter Field:=1, _
Criteria1:="=07/Jul/2006", _
Operator:=xlAnd _
, Criteria2:="<=31/Aug/2006"

as long as the month is spelt and in US date order

Regards,
Peter T

"Bob Phillips" wrote in message
...
Damn US dates <g

Dim date1_value As Date
Dim date2_value As Date

Sheets("Detail").Select
date1_value = DateValue("01-Jul-2006")
date2_value = DateValue("31-Jul-2006")
Range("A1").AutoFilter Field:=1, _
Criteria1:="=" & Str(CDbl(date1_value)), _
Operator:=xlAnd, _
Criteria2:="<=" & Str(CDbl(date2_value))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Abdul" wrote in message
oups.com...
Hi,

Always I face a problem when i try to filter a range based on
date.
If
I do it manually it works fine. But when I try to do this thru
code
it
dont work.

May be there is a better way to do this?

when I recoded my action i get:

Sheets("Detail").select
Range("A1").AutoFilter Field:=1, Criteria1:="=01/07/2006",
Operator:=xlAnd _
, Criteria2:="<=31/07/2006"

and when i run this code again no records are shown !!!