This seems like a simple thing but none of us here can figure it out.

I have a date prompt that works as is, with a "=".

Range("AO1").Select

Dim myDate As Date
myDate = InputBox(Prompt:="Enter a date please")
Range("AO1").AutoFilter Field:=41, Criteria1:="=" & myDate

What I WANT to do is return a single date, the myDate. You'd think all you
have to do is remove the , but that returns nothing. Trial and error hasn't
produced a solution.

So simple, but it has us stumped ...

KarenF

This is from "Excel 2002 VBA Programmer's Reference"
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg

http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm
Search for "Range.AutoFilter" and you'll see this note:

Range.AutoFilter

The AutoFilter method of a Range object is a very curious beast. We are forced
to pass it strings for its filter criteria and hence must be aware of its string
handling behaviour. The criteria string consists of an operator (=, , <, =
etc.) followed by a value.

If no operator is specified, the "=" operator is assumed. The key issue is that
when using the "=" operator, AutoFilter performs a textual match, while using
any other operator results in a match by value. This gives us problems when
trying to locate exact matches for dates and numbers.

If we use "=", Excel matches on the text that is displayed in the cell, i.e. the
formatted number. As the text displayed in a cell will change with different
regional settings and Windows language version, it is impossible for us to
create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string according
to US formats. Hence, a search criterion of "=02/01/2001" will find all dates
on or after 1st Feb, 2001, in all locales.

We can use this to match an exact date by using two AutoFilter criteria. The
following code will give an exact match on 1st Feb, 2001 and will work in any
locale:

Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001"

============
So in your case:

Dim myStr As String
Dim myDate As Date

myStr = InputBox(Prompt:="Enter a date please")
On Error Resume Next
myDate = CDate(myStr) 'utilizes user's local date setting
If Err.Number < 0 Then
Err.Clear
MsgBox "Please enter a date"
Exit Sub
End If

'some sort of validity check
If Year(myDate) < 2000 _
Or Year(myDate) 2020 Then
MsgBox "Get serious!"
Exit Sub
End If

Range("AO1").AutoFilter _
Field:=41, _
Criteria1:="=" & Format(myDate, "mm/dd/yyyy"), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(myDate, "mm/dd/yyyy")



KarenF wrote:

This seems like a simple thing but none of us here can figure it out.

I have a date prompt that works as is, with a "=".

Range("AO1").Select

Dim myDate As Date
myDate = InputBox(Prompt:="Enter a date please")
Range("AO1").AutoFilter Field:=41, Criteria1:="=" & myDate

What I WANT to do is return a single date, the myDate. You'd think all you
have to do is remove the , but that returns nothing. Trial and error hasn't
produced a solution.

So simple, but it has us stumped ...

KarenF

--

Dave Peterson

Curious beast indeed. Your fix worked!

"Dave Peterson" wrote:

This is from "Excel 2002 VBA Programmer's Reference"
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg

http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm
Search for "Range.AutoFilter" and you'll see this note:

Range.AutoFilter

The AutoFilter method of a Range object is a very curious beast. We are forced
to pass it strings for its filter criteria and hence must be aware of its string
handling behaviour. The criteria string consists of an operator (=, , <, =
etc.) followed by a value.

If no operator is specified, the "=" operator is assumed. The key issue is that
when using the "=" operator, AutoFilter performs a textual match, while using
any other operator results in a match by value. This gives us problems when
trying to locate exact matches for dates and numbers.

If we use "=", Excel matches on the text that is displayed in the cell, i.e. the
formatted number. As the text displayed in a cell will change with different
regional settings and Windows language version, it is impossible for us to
create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string according
to US formats. Hence, a search criterion of "=02/01/2001" will find all dates
on or after 1st Feb, 2001, in all locales.

We can use this to match an exact date by using two AutoFilter criteria. The
following code will give an exact match on 1st Feb, 2001 and will work in any
locale:

Range("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001"

============
So in your case:

Dim myStr As String
Dim myDate As Date

myStr = InputBox(Prompt:="Enter a date please")
On Error Resume Next
myDate = CDate(myStr) 'utilizes user's local date setting
If Err.Number < 0 Then
Err.Clear
MsgBox "Please enter a date"
Exit Sub
End If

'some sort of validity check
If Year(myDate) < 2000 _
Or Year(myDate) 2020 Then
MsgBox "Get serious!"
Exit Sub
End If

Range("AO1").AutoFilter _
Field:=41, _
Criteria1:="=" & Format(myDate, "mm/dd/yyyy"), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(myDate, "mm/dd/yyyy")



KarenF wrote:

This seems like a simple thing but none of us here can figure it out.

I have a date prompt that works as is, with a "=".

Range("AO1").Select

Dim myDate As Date
myDate = InputBox(Prompt:="Enter a date please")
Range("AO1").AutoFilter Field:=41, Criteria1:="=" & myDate

What I WANT to do is return a single date, the myDate. You'd think all you
have to do is remove the , but that returns nothing. Trial and error hasn't
produced a solution.

So simple, but it has us stumped ...

KarenF

--

Dave Peterson