Using autofilter with variables for multiple criteria...

[chazrab]

I recorded a macro to use autofilter to find only two values in column G. Doing so,
I manually typed in the two values and used the 'contains' and 'or' parameters to find the
values. It worked great. Below is the code:

Application.EnableEvents = False
With Sheets("REGISTER").Range("B1").CurrentRegion.Offset(1, 0)
.AutoFilter Field:=7, Criteria1:= _
        "=*table*", Operator:=xlOr, Criteria2:="=*wood*"
.SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
.AutoFilter
End With
AUTOFILTERMEMO.Show
Application.EnableEvents = True

Here's the problem: I want to use variables in this same code from two Comboboxes
on a userform so I can input ANY value I want. So I came up with this:

'Dim val1 As String
'Dim val2 As String
'val1 = MEMOSEARCH.ComboBox1.Value
'val2 = MEMOSEARCH.ComboBox2.Value
'MsgBox val1 & "   " & val2
'Application.EnableEvents = False
'With Sheets("REGISTER").Range("B1").CurrentRegion.Offset(1, 0)
'.AutoFilter Field:=7, Criteria1:=val1, Operator:=xlOr, Criteria2:=val2
'.SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
'.AutoFilter
'End With
'AUTOFILTERMEMO.Show
'Application.EnableEvents = True

I get an empty REPORT sheet using this code when I should get exactly the same result.
Why this doesn't work, I have no idea. I tired all variations of " ", = and *,'s but no combination reads the variables and runs
the code to produce the same result. The Msgbox reads the correct values. Any idea why the code is not reading and using
the value of the variables to give the same result as the recorded macro in the first code block ?
Sorry for the long explanation. Tried to be as brief but as complete as possible.

Thanks for anyone's help.
cr

 

[JLGWhiz]

Maybe this

.AutoFilter Field:=7, Criteria1:="=" & val1, Operator:=xlOr, Criteria2:="=" & val2

 

[chazrab]

Maybe this

.AutoFilter Field:=7, Criteria1:="=" & val1, Operator:=xlOr, Criteria2:="=" & val2

Thanks - but still producing the same result - it does not find any values. Only the first row, for some reason, is copied to the REPORT sheet and column 7 of that row has no values equal to either variable.
cr

 

[Peter_SSs]

Bit tricky not knowing what your data actually looks like or what you are typing into the comboboxes, but does this work for you?

.AutoFilter Field:=7, Criteria1:="*" & val1 & "*", Operator:=xlOr, Criteria2:="*" & val2 & "*"

 

[chazrab]

Bit tricky not knowing what your data actually looks like or what you are typing into the comboboxes, but does this work for you?

.AutoFilter Field:=7, Criteria1:="*" & val1 & "*", Operator:=xlOr, Criteria2:="*" & val2 & "*"

...OK Peter - this works perfectly now - it seems from you rewrite it's a matter of correctly writing and placing the "", * and & when using a variable assignment. Many thanks

cr

 

[Peter_SSs]

...OK Peter - this works perfectly now - it seems from you rewrite it's a matter of correctly writing and placing the "", * and & when using a variable assignment. Many thanks

cr

Glad it is going now.