Using Autofilter with variables combined with xlOr

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
729
...doesn't seem to work, but does work if the actual values are hard coded in. Here's what I mean:
Code:
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
 

Attachments

  • Userform1.jpg
    Userform1.jpg
    33 KB · Views: 2

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,250
Office Version
  1. 365
Platform
  1. Windows
Perhaps this?

VBA Code:
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
 
Solution

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
729
Perhaps this?

VBA Code:
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
 
Last edited:

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,250
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 28, 2020
Messages
1,250
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 21, 2006
Messages
729
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.
 

Forum statistics

Threads
1,181,212
Messages
5,928,724
Members
436,625
Latest member
agholson81

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top