Help with InputBox in a Macro
 

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

Help with InputBox in a Macro
 

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

Help with InputBox in a Macro
 

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" wrote in
message ...

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

Help with InputBox in a Macro
 

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" wrote in
message ...

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