Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto filter within a macro
I have a macro that presently filters a spreadsheet by a date that is input
in a input box as the macro is running, How can I do the same but for text. I have tried to change what I thought woulkd be right but with no look. I enclose a copy of the date filter lines from within the macro columns I can change once I know where I am going wrong FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, Criteria1:=FilterDate I would be most grateful if anyone can help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto filter within a macro
Dates are odd. Try
Dim FilterDate FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, _ Criteria1:=Format(FilterDate, Selection.Cells(2, 1).NumberFormat) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sean" wrote in message ... I have a macro that presently filters a spreadsheet by a date that is input in a input box as the macro is running, How can I do the same but for text. I have tried to change what I thought woulkd be right but with no look. I enclose a copy of the date filter lines from within the macro columns I can change once I know where I am going wrong FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, Criteria1:=FilterDate I would be most grateful if anyone can help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto filter within a macro
the date bit works fine, I want to filter another column by a text (rollout
weeks) these are indicated in the column as R1 / R2 / R3 etc which is formatted as "gemeral" what I need to know is what I need to change FilterDate = CDate( to make it filter another column bu rollout week? and presumably I would change the end of the second line Criteria1:=FilterDate to reflect the change at the start of the line above. Sean... "Bob Phillips" wrote: Dates are odd. Try Dim FilterDate FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, _ Criteria1:=Format(FilterDate, Selection.Cells(2, 1).NumberFormat) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sean" wrote in message ... I have a macro that presently filters a spreadsheet by a date that is input in a input box as the macro is running, How can I do the same but for text. I have tried to change what I thought woulkd be right but with no look. I enclose a copy of the date filter lines from within the macro columns I can change once I know where I am going wrong FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, Criteria1:=FilterDate I would be most grateful if anyone can help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto filter within a macro
Hi Sean
Change the variable to a string to receive your R1, R2 etc. Change the criteria to represent what you have called this new variable, and change the field number to represent the dropdown number that relates to the entries with R1, R2 etc. In the example below I have used field 10 instead of 9. Dim RolloutWeek as String RolloutWeek = Application.InputBox("Enter Rollout Week e.g. R1 ") Selection.AutoFilter Field:=10, Criteria1:=RolloutWeek -- Regards Roger Govier "Sean" wrote in message ... the date bit works fine, I want to filter another column by a text (rollout weeks) these are indicated in the column as R1 / R2 / R3 etc which is formatted as "gemeral" what I need to know is what I need to change FilterDate = CDate( to make it filter another column bu rollout week? and presumably I would change the end of the second line Criteria1:=FilterDate to reflect the change at the start of the line above. Sean... "Bob Phillips" wrote: Dates are odd. Try Dim FilterDate FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, _ Criteria1:=Format(FilterDate, Selection.Cells(2, 1).NumberFormat) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sean" wrote in message ... I have a macro that presently filters a spreadsheet by a date that is input in a input box as the macro is running, How can I do the same but for text. I have tried to change what I thought woulkd be right but with no look. I enclose a copy of the date filter lines from within the macro columns I can change once I know where I am going wrong FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, Criteria1:=FilterDate I would be most grateful if anyone can help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto filter within a macro
Hi BOB,
I also tried your code and it works great. Is there any way I can now get the filter to show me everything LESS THAN the date I type in the message box? I tried Criteria="<Format(FilterDate, Selection.Cells(2, 1).NumberFormat)" but it doesn't work. Any ideas? Thanks Richard "Sean" wrote: the date bit works fine, I want to filter another column by a text (rollout weeks) these are indicated in the column as R1 / R2 / R3 etc which is formatted as "gemeral" what I need to know is what I need to change FilterDate = CDate( to make it filter another column bu rollout week? and presumably I would change the end of the second line Criteria1:=FilterDate to reflect the change at the start of the line above. Sean... "Bob Phillips" wrote: Dates are odd. Try Dim FilterDate FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, _ Criteria1:=Format(FilterDate, Selection.Cells(2, 1).NumberFormat) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sean" wrote in message ... I have a macro that presently filters a spreadsheet by a date that is input in a input box as the macro is running, How can I do the same but for text. I have tried to change what I thought woulkd be right but with no look. I enclose a copy of the date filter lines from within the macro columns I can change once I know where I am going wrong FilterDate = CDate(Application.InputBox("Enter Filter date")) Selection.AutoFilter Field:=9, Criteria1:=FilterDate I would be most grateful if anyone can help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell protection & Auto Filter | Excel Worksheet Functions | |||
Cell reference in the auto filter custom list | Excel Discussion (Misc queries) | |||
Macro for Filter Switches ... Maybe??? | Excel Discussion (Misc queries) | |||
Filter Switches vs Macro? | Excel Discussion (Misc queries) | |||
The Auto Filter button lost the column specified option. | Excel Worksheet Functions |