CODEWITHSUNDEEP
excelvbafilter
MichaelS
MichaelS

Reputation: 11

Filter Row containing cell value

I'm looping through each cell in one sheet and for each value I need to filter a column in another sheet for each value containing the cell value (IE: if my cell is 1234 I want to display every cell in the other sheet containing "1234" including "m1234", "12345" and "91234f").

My problem is that wild cards don't seem to behave as advertised.

I've tried filtering with "*" & cell.value & "*" but the asterisk seems to require that something occupy its space instead of allowing empty strings. I tried using multiple criteria with Arrays to handle various cases but I can't get all three conditions to be satisfied.

The idea here was that I'd catch any value that begins with, ends with or is equal to the value I'm filtering for.

.Range("A:A").AutoFilter Field:=31, Criteria1:= _    
Array("*" & cell.value, cell.value, cell.value & "*"), Operator:=xlFilterValues

In the column I'm filtering I have the following values:

836218
836218m
m836218
9836218
8362189
m8362189

The cell.value I'm comparing against is "836218".

My filter only returns exact matches.

Upvotes: 0

Views: 1357

Answers (1)

MichaelS
MichaelS

Reputation: 11

I managed to get the results I wanted with this. I could've sworn I tried this already but after posting I tried it again and it worked.

.Range("A:A").AutoFilter Field:=31, Criteria1:="*" & cell.Value & "*", Operator:=xlOr

Upvotes: 1

Related Questions