Using Autofilter with variables combined with xlOr

[chazrab]

...doesn't seem to work, but does work if the actual values are hard coded in. Here's what I mean:

Private Sub cmdFINDVAL_Click()
Dim val1, val2 As String
val1 = Userform1.TextBox1.Value = last days
val2 =Userform1.TextBox2.Value = latter days
Range("A1:A10").Select
Selection.AUTOFILTER
ActiveSheet.Range("$A$1:$A$10").AUTOFILTER Field:=1, Criteria1:= _
        "*val1*", Operator:=xlOr, Criteria2:="*val2*"
End Sub
does not work.  However, using the actual value between quotes does work when done like this:

ActiveSheet.Range("$A$1:$A$10").AUTOFILTER Field:=1, Criteria1:= _
        "*last days*", Operator:=xlOr, Criteria2:="*latter days*"

Words are within the cell text, i.e., substings, hence asteriks before and after.
So this code just asks Excel to find all occurrences within all cell text down col A that contain
last days or latter days.

I want the input from Userform textboxes like this:

Please help if you see what I'm doing wrong. This should be very easy to accomplish
Thanks, cr

 

[kevin9999]

Perhaps this?

Private Sub cmdFINDVAL_Click()
    Dim val1 As String, val2 As String
    val1 = UserForm1.TextBox1.Value
    val2 = UserForm1.TextBox2.Value
    
    With ActiveSheet.Range("A1")
        .AutoFilter 1, "*" & val1 & "*", 2, "*" & val2 & "*"
    End With
End Sub

 

[chazrab]

Perhaps this?

Private Sub cmdFINDVAL_Click()
    Dim val1 As String, val2 As String
    val1 = UserForm1.TextBox1.Value
    val2 = UserForm1.TextBox2.Value
  
    With ActiveSheet.Range("A1")
        .AutoFilter 1, "*" & val1 & "*", 2, "*" & val2 & "*"
    End With
End Sub

Hi Kevin and thanks a mil - well, as you might expect, it works great. Seems it's just a matter of putting quotes and asterisks in the right
places. I could find no documentation that shows how to do this [i.e., quotes and asterisks, commas, etc. in the right places] correctly. Would save people who want to do this same thing lots of time, avoiding experimentation if somebody would provide this on YT or elsewhere. Thanks again, I knew it had to be something simple I was missing.
cr

 

[kevin9999]

Hi Kevin and thanks a mil - well, as you might expect, it works great. Seems it's just a matter of putting quotes and asterisks in the right
places. Thanks again,
cr

You're welcome, and thanks for the feedback.

 

[kevin9999]

Hi Kevin and thanks a mil - well, as you might expect, it works great. Seems it's just a matter of putting quotes and asterisks in the right
places. I could find no documentation that shows how to do this [i.e., quotes and asterisks, commas, etc. in the right places] correctly. Would save people who want to do this same thing lots of time, avoiding experimentation if somebody would provide this on YT or elsewhere. Thanks again, I knew it had to be something simple I was missing.
cr

Actually, if you mark post #2 as a solution then others would be able to find it easier on this site?

 

[chazrab]

Actually, if you mark post #2 as a solution then others would be able to find it easier on this site?

Done. Not sure if I did it correctly, though - I just clicked the checkmark on the right and it turned green.