Help please with autofilter and dates

[Jim]

I'm having some trouble using vba to to autofilter some data based on the
date in column A of worksheet named Test
The filter appears to work if I do it manually
Column A is formatted as a date (dd/mm/yyyy)
Would one (or more ) of you gurus please assist by pointing out the error(s)
in the code below

Windows XP Pro
MS Excel 2003

Regards and TIA
Jim Burton
-----------------------

Dim StartDate As Date
Dim EndDate As Date

StartDate = Format(InputBox("Enter Start date for the report" ),
"dd/mm/yyyy")

'error traps removed for clarity

EndDate = Format(InputBox("Enter End date for the report"), "dd/mm/yyyy")

'error traps removed for clarity

With Sheets("Test)")
If AutoFilterMode = True Then .AutoFilterMode = False
.Columns("A:G").Select
With Selection
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & StartDate,
Operator:=xlAnd, Criteria2:="<=" & EndDate
Selection.AutoFilter Field:=3, Criteria1:="<>"
Selection.AutoFilter Field:=7, Criteria1:="="
End With

With Sheets("Test").PageSetup
.PrintArea = "$A:$F"
.CenterHeader = "&""Comic Sans MS,Bold Italic""&14Test Macro"
End With
ScreenUpdating = True
Sheets("Test").PrintPreview
ScreenUpdating = False
.AutoFilterMode = False
End With

 

[Dave Peterson]

Filtering dates in code can be a miserable experience.

I used to screw around with things like:
...., criteria1:=clng(dateserial(2007,7,1), ...

Or by picking up the numberformat from the first cell in the field that has a
date.

But Ron de Bruin pointed me to a way that works for him (he can have a problem
with international issues).

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/ExcelProgRef/Ch22/ProgRefCh22.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("A1200").AutoFilter 2, ">=02/01/2001", xlAnd, "<=02/01/2001"

===============
So you'll want to parse your text to change the order to match the USA settings
(mm/dd/yyyy).

And another question...

Aren't you worried that the user will enter 01/02/03 in the inputbox and you'll
assume that he/she means Feb 1, 2003 when you change things around.

You may want to drop the inputbox and use a calendar control (on a userform???)
so that your users (and you as a developer), never have to worry about those
ambiguous dates -- or even if they typed in a date!

If you want to design your own userform:

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even use 3 textboxes/comboboxes/spinners/scrollbars.
One for the month, one for the day and one for the year.

 

[Jim]

Thanks Dave,
I used Format to change the Criteria for Field1 to US date format as follows
and it appears to be working ok

Selection.AutoFilter Field:=1, _
Criteria1:=">=" & Format(StartDate, "mm/dd/yyyy"), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(EndDate, "mm/dd/yyyy")

Regards
Jim Burton

 

[Dave Peterson]

The ability for the user to enter an ambiguous date still scares me.

 

[Jim]

Hmm ... me too
I'm still working on that

The ability for the user to enter an ambiguous date still scares me.

 

[Jim]

Dave,
I have amended the code to include some error traps and a message box to
display the selection criteria as below (I still have to include error traps
for invalid dates) but so far it seems to work ok ...
e.g. I have entered the date 11/01/2008 and it is recognised as 11 Jan 2008

Can you forsee any problems ?

Thank you for you help
Jim
-------------------------
' Initialise defaults

StartDate = DateAdd("m", -2, Date)
StartDate = DateAdd("d", -3, StartDate)

' Specify date criteria

Setdates:
StartDate = Format(InputBox("Enter Start date for the report as
dd/mm/yyyy", , StartDate), "dd/mm/yy")
EndDate = Format(InputBox("Enter End date for the report as dd/mm/yyyy",
, EndDate), "dd/mm/yy")

If StartDate > EndDate Then
MsgBox "Startdate must be earlier than EndDate"
GoTo Setdates
End If
If StartDate >= Date Then
MsgBox "Error 2 Startdate must be earlier than today"
GoTo Setdates
End If
If EndDate > Date Then
MsgBox "Enddate cannot be greater than today"
GoTo Setdates
End If

Response = MsgBox("Report will cover the period from " &
Format(StartDate, "dd mmm yyyy") & " to " & Format(EndDate, "dd mmm yyyy"),
vbOKCancel)
If Response = vbCancel Then GoTo Setdates

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

Hmm ... me too
I'm still working on that

 

[Dave Peterson]

Lots of people won't read your instructions for formatting. You may want to
look into using a calendar control.

But I'd convert the strings from the inputboxes to date (cDate()). Using
Application.inputbox means that I can specify that I want a number (and dates
are numbers to excel). So I could drop some of the checking.

Dim myDate as Variant 'string or date
'only accept a number
mydate = application.inputbox(prompt:="enter a date",type:=1)
if mydate = 0 then
'user hit cancel
exit sub
end if
mydate = cdate(mydate)

'some minor testing
if year(mydate) < 2008 then
msgbox "not a valid year
exit sub '??
end if

But IIRC, cDate() will use the windows short date setting for the user.

Dave,
I have amended the code to include some error traps and a message box to
display the selection criteria as below (I still have to include error traps
for invalid dates) but so far it seems to work ok ...
e.g. I have entered the date 11/01/2008 and it is recognised as 11 Jan 2008

Can you forsee any problems ?

Thank you for you help
Jim
-------------------------
' Initialise defaults

StartDate = DateAdd("m", -2, Date)
StartDate = DateAdd("d", -3, StartDate)

' Specify date criteria

Setdates:
StartDate = Format(InputBox("Enter Start date for the report as
dd/mm/yyyy", , StartDate), "dd/mm/yy")
EndDate = Format(InputBox("Enter End date for the report as dd/mm/yyyy",
, EndDate), "dd/mm/yy")

If StartDate > EndDate Then
MsgBox "Startdate must be earlier than EndDate"
GoTo Setdates
End If
If StartDate >= Date Then
MsgBox "Error 2 Startdate must be earlier than today"
GoTo Setdates
End If
If EndDate > Date Then
MsgBox "Enddate cannot be greater than today"
GoTo Setdates
End If

Response = MsgBox("Report will cover the period from " &
Format(StartDate, "dd mmm yyyy") & " to " & Format(EndDate, "dd mmm yyyy"),
vbOKCancel)
If Response = vbCancel Then GoTo Setdates

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

 

[Jim]

Dave,
Thank you for your assistance

The code is looking more professional and elegant now ...
I had started to use IsDate() to check for a valid string in the Inputbox
and it seemed to do the job but I have changed it now to cDate().
and included a check to verify that the date is valid (i.e. that it no more
earlier than 3 months from the system date ) but with an option to accept
and continue.
I have also included the application.inputbox Type:=1

I should perhaps mention that the code will only be used by myself and one
other person who will normally be working in the same office as myself.

Regards
Jim

Lots of people won't read your instructions for formatting. You may want
to
look into using a calendar control.

But I'd convert the strings from the inputboxes to date (cDate()). Using
Application.inputbox means that I can specify that I want a number (and
dates
are numbers to excel). So I could drop some of the checking.

Dim myDate as Variant 'string or date
'only accept a number
mydate = application.inputbox(prompt:="enter a date",type:=1)
if mydate = 0 then
'user hit cancel
exit sub
end if
mydate = cdate(mydate)

'some minor testing
if year(mydate) < 2008 then
msgbox "not a valid year
exit sub '??
end if

But IIRC, cDate() will use the windows short date setting for the user.