Filter problems
Posted by Paul Magruder on June 05, 2001 4:50 AM
I am using a Inputbox to input the date, that is used to filter my data. The problem is somtimes the filter does not reconize the date entered,and excel hangs up, even though I have formated them the same. I am using a macro to turn on the filters and run the InputBox.
whatdate = InputBox("Todays date? ex: type ->2/15")
Selection.AutoFilter Field:=22, Criteria1:=whatdate
The formula I am using to bring in the date for the data is =today()-1 (to reference the previous Days Data)..... Excel 97....WIndows NT...
Thanks in advance
Paul
Posted by Mark W. on June 05, 2001 5:28 AM
Just a guess... but did you convert whatdate to a
date value rather than a text string?
Posted by Paul Magruder on June 05, 2001 8:02 AM
Not sure what you mean....
Posted by Barrie Davidson on June 05, 2001 8:34 AM
Paul, try changing your syntax to:
whatdate = InputBox("Todays date? ex: type ->2/15",,,,,,,1)
This specifies that the input box returns a number data type. Also, make sure that your variable "whatdate" is not declared as a string.
Hope this helps you out.
Regards,
Barrie
Posted by Mark W. on June 05, 2001 8:35 AM
Okay, try this instead...
Type: =2/15/01
...and see what happens.
Posted by Paul Magruder on June 06, 2001 5:15 AM
Now I get.... Compile Error....Wrong number of arguments or invalid property assignment
whatdate = InputBox("Todays date? ex: type ->2/15", , , , , , , 1)
Posted by Mark W. on June 06, 2001 6:09 AM
Paul, when I run the Macro Recorder while emulating
your desired AutoFilter it seems to want an
argument like "=2/15/01". In my earlier posting
I suggesting that it might be looking for a numeric
value. I was clearly wrong!
Posted by Barrie Davidson on June 06, 2001 6:41 AM
Sorry about that Paul. Try this instead (I've also incorporated a check to confirm that the input is actually a date).
Top:
whatdate = CDate(InputBox("Todays date? ex: type ->2/15"))
If IsDate(whatdate) = True Then
'do nothing
Else
MsgBox ("A proper date was not entered")
GoTo Top
End If
If you have any questions just let me know
Regards,
Barrie
