Change criteria in a CF

[CC]

Hi to all
I've did a CF like the example below
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=*WARNING*", Operator:=xlAnd
Selection.AutoFilter Field:=9, Criteria1:="=*Pressure*", Operator:=xlAnd

Is possible change the criteria withoult change the macro e.g insert
the criteria word in a cell P1 .
Appreciate any help

CC

 

[Luke M]

Something like this:

Selection.AutoFilter Field:=3, Criteria1:="=*" & Range("P1")Value & "*"

 

[Jim Thomlinson]

Not 100% sure what you want here. Did you want the filter to change when
someone changes the value in Cell P1? You r current filter has 2 criteria.
Which on eis this???

 

[Bernie Deitrick]

You need to have Worksheet change event code to re-apply the filter when cell P1 changes, and change
the code from

Criteria1:="=*WARNING*"

to

Criteria1:="=*" & Worksheet("SName").Range("P1").VAlue * "*"


HTH,
Bernie
MS Excel MVP

 

[CC]

Both

Not 100% sure what you want here. Did you want the filter to change when
someone changes the value in Cell P1? You r current filter has 2 criteria.
Which on eis this???

 

[Jim Thomlinson]

Here is some code that might do the trick. I do not know which range you want
filtered so this is the best I can give you for now...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address = "$P$1" then
with Range("A1:N10000")
.AutoFilter
.AutoFilter Field:=3, Criteria1:="=*" & .range("P1").value & "*",
Operator:=xlAnd
.AutoFilter Field:=9, Criteria1:="=*Pressure*", Operator:=xlAnd
end with
end if
end sub

This code needs to be placed in the worksheet. Right click the sheet tab and
select view code. Paste the code in there...