auto filter within a macro

[Guest]

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

 

[Bob Phillips]

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)

 

[Roger Govier]

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

 

[Rich]

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