Replace criteria with a cell reference

[rjpiekos]

I got this code from another web site and it does almost everything i need but...
i would like to replace the criteria1 reference (currently displayed as ="a") with the cell reference c4
in other words i would enter a value in cell C4 and the macro would use that value to filter on field 2.
i don't need criteria 2.

Thanks for your help


Sub Custom1()
Range("A1").AutoFilter Field:=2, Criteria1:="a", Operator:=xlOr, _
Criteria2:="b"
End Sub

 

[Joe4]

Welcome to the Board!

Try:

Sub Custom1()
    Range("A1").AutoFilter Field:=2, Criteria1:=Range("C4").Text
End Sub

 

[rjpiekos]

thanks but there is an "oops" (there is always an oops)

the value in C4 is only a partial match so somehow you have to put wild card characters around the value in C4
the filter i'm using is "contains . . . "

 

[Joe4]

If you use the Macro Recorder, it will give you a clue what you need to do (you'll see what "contains" looks like in VBA code).
So, you code would look like:

Sub Custom1()
    Range("A1").AutoFilter Field:=2, Criteria1:="*" & Range("C4").Text & "*"
End Sub

 

[rjpiekos]

Thanks for response. i did use the recorder but i was stuck on the correct format for the "* cell ref *". the recorder provided an example that did not translate well to my situation. I seem to have the most problems trying to use the range and cell commands when identifying a specific cell. your example of "range("C4").Text" was new to me.

Looks like a great web site.

 

[Joe4]

There's just a few concepts here at play, that I think if you understand, will make a lot more sense.

In VBA, it is important to understand the difference between literal text and variables.

Criteria1:="a"

Anything enclosed in double-quotes is treated as literal text (literally, the value a).

Criteria1:=a

In this example, a represent a variables (not literal text). So a must be defined (set to something). So before this section of code, you may see something like this:

a=Range("C4")

This means that the variable a is equal to whatever the value in cell C4 is.

So that is a brief description between the difference between variables and literal text.

Now, if you used the Macro Recorder, and recorded yourself filtering a data range for values that contain "a", and then looked at the code you just recorded, you would see criteria like this:

Criteria1:="*a*"

The asterisks indicate "wildcards". So what that means, is look for entries where it has "something" than and "a" and then something else after it (note that "something" can be "nothing). So it would return anything that has an "a" anywhere in it.

So now we have identified the pattern we want: asterisk + what I am looking for + asterisk

Now, we already know from my first response, that in order to use C4 as our criteria, we use:

Criteria1:=Range("C4").Text

So now we just need to attach literal asterisks for the wild cards on each side of it. And to sew together multiple string items, we use the & symbol.
So it would look like:"

Criteria1:="*" & Range("C4").Text & "*"

So hopefully, that makes sense, and you can see how we arrived there and how you can use the Macro Recorder to help figure out some of the code for you. It really is a helpful tool. Even us programmers who have been doing this for years use it often (as we don't have all the syntax of VBA committed to memory!).