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

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

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

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

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