Wildcards & Cell References in Filters
 

Hi, hopefully a quick answer is available to this, just having a mental blank:

This code succesfully finds all values that contain Australia. Easy.

Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="=*Australia*", Operator:= _
xlAnd

However, i need to filter based on the value of a cell (not always Australia)

Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="=*C12*", Operator:= _
xlAnd

Would\should\could this work?

Wildcards & Cell References in Filters
 

Example using just simple filter with wildcards:-

Dim selectCriteria
selectCriteria = "*" & Range("C12") & "*"
Selection.AutoFilter Field:=1, Criteria1:=selectCriteria

Build the criteria by concatenating the wild cards with the other data and
save in a variable. You don't use the quotes when replacing the criteria with
a variable even if that variable is a cell value.

Regards,

OssieMac

"D Zandveld" wrote:

Hi, hopefully a quick answer is available to this, just having a mental blank:

This code succesfully finds all values that contain Australia. Easy.

Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="=*Australia*", Operator:= _
xlAnd

However, i need to filter based on the value of a cell (not always Australia)

Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="=*C12*", Operator:= _
xlAnd

Would\should\could this work?

Wildcards & Cell References in Filters
 

Another example with an Or operator in the filter just in case you have
problems with it.

selectCriteria1 = "=*" & Range("C12") & "*"
selectCriteria2 = "=*" & Range("C13") & "*"

Selection.AutoFilter Field:=1, Criteria1:=selectCriteria1, _
Operator:=xlOr, Criteria2:=selectCriteria2

Regards,

OssieMac


"OssieMac" wrote:

Example using just simple filter with wildcards:-

Dim selectCriteria
selectCriteria = "*" & Range("C12") & "*"
Selection.AutoFilter Field:=1, Criteria1:=selectCriteria

Build the criteria by concatenating the wild cards with the other data and
save in a variable. You don't use the quotes when replacing the criteria with
a variable even if that variable is a cell value.

Regards,

OssieMac

"D Zandveld" wrote:

Hi, hopefully a quick answer is available to this, just having a mental blank:

This code succesfully finds all values that contain Australia. Easy.

Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="=*Australia*", Operator:= _
xlAnd

However, i need to filter based on the value of a cell (not always Australia)

Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="=*C12*", Operator:= _
xlAnd

Would\should\could this work?