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))



Lizz45ie wrote:
>
> 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.
>
> --
> Lizz45ie
> ------------------------------------------------------------------------
> Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
> View this thread: http://www.excelforum.com/showthread...hreadid=479966

--

Dave Peterson

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


---
Regards,
Norman


"Lizz45ie" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> Lizz45ie
> ------------------------------------------------------------------------
> Lizz45ie's Profile:
> http://www.excelforum.com/member.php...o&userid=23410
> View this thread: http://www.excelforum.com/showthread...hreadid=479966
>

[Dave Peterson]

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



Norman Jones wrote:
>
> 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.
>
> ---
> Regards,
> Norman
>
> "Lizz45ie" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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.
> >
> >
> > --
> > Lizz45ie
> > ------------------------------------------------------------------------
> > Lizz45ie's Profile:
> > http://www.excelforum.com/member.php...o&userid=23410
> > View this thread: http://www.excelforum.com/showthread...hreadid=479966
> >

--

Dave Peterson