Help with InputBox in a Macro

[Lizz45ie]

I'm used the Macro recorder to create a new Macro to extract data out of
my master worksheet. The macro worked until I added the "InputBox " to
my macro.
The inputbox prompt me for the dates but it doesn't return any records
for the specified timeframe. Please see the code I'm using to retrieve
the records.

Starting = InputBox(Prompt:="Enter Starting Date as mm/dd/yyyy")
Ending = InputBox(Prompt:="Enter Ending Date as mm/dd/yyyy")
Selection.AutoFilter Field:=37, Criteria1:=Starting,
Operator:=xlAnd _
, Criteria2:=Ending
Selection.AutoFilter Field:=39, Criteria1:="ZMO"
Selection.AutoFilter Field:=35, Criteria1:="A"

Thanks for any help.

 

[Dave Peterson]

Sometimes this kind of thing works:

Selection.AutoFilter Field:=37, _
Criteria1:=clng(cdate(Starting)), _
Operator:=xlAnd, clng(cdate(Criteria2:=Ending))

 

[Norman Jones]

Hi Lizzie45ie,

I think there was a typo in Dave's response.I think he intended:

Selection.AutoFilter Field:=37, _
Criteria1:=CLng(CDate(Starting)), _
Operator:=xlAnd, Criteria2:=CLng(CDate(Ending))

However, I think that you will also need to add the > and < operators, e.g.:

Selection.AutoFilter Field:=37, _
Criteria1:=" >=" & CLng(CDate(Starting)), _
Operator:=xlAnd, _
Criteria2:="<=" & CLng(CDate(Ending))

Incidentally, with the Starting and Ending variables dimmed as dates, I was
able to drop the CLng and CDate conversions when woking with a US date
setup; If I used UK type (dd/mm/yy) date setings, then I needed the CLng
conversions. In either case it will not hurt to use the conversions.

 

[Dave Peterson]

Thanks for the typo correction and for the logic correction, too!