Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputbox macro for multiple cells | Excel Discussion (Misc queries) | |||
Advanced Filter Macro with InputBox use | Excel Programming | |||
Macro InputBox with Drop-Down List? | Excel Programming | |||
Macro InputBox with Drop-Down List? | Excel Programming | |||
Excel Macro Code invoking InputBox. | Excel Programming |