Help with InputBox in a Macro


L

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.
 
Ad

Advertisements

D

Dave Peterson

Sometimes this kind of thing works:

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

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

excel date format problem 1
Autofilter in a macro 1
Filter Problem 2
Autofilter - Contains 3
Apply macro to all worksheets 15
Custom filter 26
AutoFilter... not quite right 3
Filtering and user input 4

Top