VBA Autofilter variable criterion

H

Human_doing

Board Regular
Joined
Feb 16, 2011
Messages
137
Hi all,

Can anyone please help with this code, what I'm looking to do is create a message box that asks user to choose a value and then autofilter returns all rows where the input criterion appears at any point in the cell. The below code only returns rows where the cell exactly matches the criterion. Any help much appreciated,

Thanks

Code: 
Dim MyInput1 As Variant
        MyInput1 = InputBox("To which employee are you wishing to address this email? Correctly enter first name and surname, ", _
        "Enter Employee name", Format(Text, ""))
    MsgBox "Employee selected is " & MyInput1
 
    'Autofilter section
    Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:= _
        ">=" & MyInput1, Operator:=xlAnd, Criteria2:="<=" & MyInput1
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
One criteria seems to cancel the other out.

The first looks for greater than or equal to and the second looks for less than or equal to.

If you want more than one match then one result would return greater than or less than the other, cancelling it out.

Your code is asking for an exact match only to MyInput1.

Cheers
 
Last edited:
Upvote 0
Because that match for the two criterion.

say your input is 5 then the filter find 6 it returns 6 and the second criteria cancels it out because it is not less than 5.

So both criterion will return 5 because it matches both criterion as = to Input1.
 
Last edited:
Upvote 0
Hi guys,

thanks so much for your help on this. I have revised the code to the below but it's still only including rows where the cell matches exactly the user input, whereas what I'm looking for is for rows to be returned where the user input occurs anywhere in the cell.

Current code:

Code: 
 Dim MyInput1 As Variant
        MyInput1 = InputBox("To which employee are you wishing to address this email? Correctly enter first name and surname DO NOT use Mr etc..", _
        "Enter Employee name", Format(Text, ""))
    MsgBox "Employee selected is " & MyInput1
 
    'Autofilter the data based on what the human being put in                   
Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:= _
        "*" & MyInput1 & "*"
 
    'Add selected data to new sheet
     Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
 
Upvote 0
Look at this (I used your code) :

Note: I only enable the code until the filter.

Code: 
Sub Test()
Dim MyInput1 As Variant
        MyInput1 = InputBox("To which employee are you wishing to address this email? Correctly enter first name and surname DO NOT use Mr etc..", _
        "Enter Employee name", Format(Text, ""))
    MsgBox "Employee selected is " & MyInput1
 
    'Autofilter the data based on what the human being put in
    Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:= _
        "*" & MyInput1 & "*"
 
    'Add selected data to new sheet
    ' Cells.Select
    'Selection.Copy
    'Sheets.Add After:=Sheets(Sheets.Count)
    'ActiveSheet.Paste
End Sub

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col01 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col02 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col03 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col04 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col05 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">Data01</TD><TD style="TEXT-ALIGN: center">430</TD><TD style="TEXT-ALIGN: center">Dpto01</TD><TD style="TEXT-ALIGN: center">Justin Denver</TD><TD style="TEXT-ALIGN: center">email@0001</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">Data02</TD><TD style="TEXT-ALIGN: center">563</TD><TD style="TEXT-ALIGN: center">Dpto02</TD><TD style="TEXT-ALIGN: center">Robert Astley</TD><TD style="TEXT-ALIGN: center">email@0002</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">Data03</TD><TD style="TEXT-ALIGN: center">487</TD><TD style="TEXT-ALIGN: center">Dpto03</TD><TD style="TEXT-ALIGN: center">Tina Gold</TD><TD style="TEXT-ALIGN: center">email@0003</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">Data04</TD><TD style="TEXT-ALIGN: center">489</TD><TD style="TEXT-ALIGN: center">Dpto04</TD><TD style="TEXT-ALIGN: center">Paul Paragony</TD><TD style="TEXT-ALIGN: center">email@0004</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">Data05</TD><TD style="TEXT-ALIGN: center">286</TD><TD style="TEXT-ALIGN: center">Dpto05</TD><TD style="TEXT-ALIGN: center">Mark Google</TD><TD style="TEXT-ALIGN: center">email@0005</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">Data06</TD><TD style="TEXT-ALIGN: center">128</TD><TD style="TEXT-ALIGN: center">Dpto01</TD><TD style="TEXT-ALIGN: center">Robert Magic</TD><TD style="TEXT-ALIGN: center">email@0006</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">Data07</TD><TD style="TEXT-ALIGN: center">498</TD><TD style="TEXT-ALIGN: center">Dpto02</TD><TD style="TEXT-ALIGN: center">Kristine Gold</TD><TD style="TEXT-ALIGN: center">email@0007</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">Data08</TD><TD style="TEXT-ALIGN: center">591</TD><TD style="TEXT-ALIGN: center">Dpto03</TD><TD style="TEXT-ALIGN: center">Robert Kosinsk</TD><TD style="TEXT-ALIGN: center">email@0008</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">Data09</TD><TD style="TEXT-ALIGN: center">487</TD><TD style="TEXT-ALIGN: center">Dpto04</TD><TD style="TEXT-ALIGN: center">Tina Google</TD><TD style="TEXT-ALIGN: center">email@0009</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">Data10</TD><TD style="TEXT-ALIGN: center">752</TD><TD style="TEXT-ALIGN: center">Dpto05</TD><TD style="TEXT-ALIGN: center">Agelina Kosinsk</TD><TD style="TEXT-ALIGN: center">email@0010</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">Data11</TD><TD style="TEXT-ALIGN: center">513</TD><TD style="TEXT-ALIGN: center">Dpto01</TD><TD style="TEXT-ALIGN: center">Paula Google</TD><TD style="TEXT-ALIGN: center">email@0011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">Data12</TD><TD style="TEXT-ALIGN: center">652</TD><TD style="TEXT-ALIGN: center">Dpto02</TD><TD style="TEXT-ALIGN: center">Robert Kosinsk</TD><TD style="TEXT-ALIGN: center">email@0012</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">Data13</TD><TD style="TEXT-ALIGN: center">531</TD><TD style="TEXT-ALIGN: center">Dpto03</TD><TD style="TEXT-ALIGN: center">Colbie Denver</TD><TD style="TEXT-ALIGN: center">email@0013</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">Data14</TD><TD style="TEXT-ALIGN: center">202</TD><TD style="TEXT-ALIGN: center">Dpto04</TD><TD style="TEXT-ALIGN: center">Karla Gold</TD><TD style="TEXT-ALIGN: center">email@0014</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center">Data15</TD><TD style="TEXT-ALIGN: center">754</TD><TD style="TEXT-ALIGN: center">Dpto05</TD><TD style="TEXT-ALIGN: center">Robert Google</TD><TD style="TEXT-ALIGN: center">email@0015</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: center">Data16</TD><TD style="TEXT-ALIGN: center">468</TD><TD style="TEXT-ALIGN: center">Dpto05</TD><TD style="TEXT-ALIGN: center">Agelina Astley</TD><TD style="TEXT-ALIGN: center">email@0016</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8">All Data</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Sheet3

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col01 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col02 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col03 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col04 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col05 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">Data02</TD><TD style="TEXT-ALIGN: center">563</TD><TD style="TEXT-ALIGN: center">Dpto02</TD><TD style="TEXT-ALIGN: center">Robert Astley</TD><TD style="TEXT-ALIGN: center">email@0002</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">Data06</TD><TD style="TEXT-ALIGN: center">128</TD><TD style="TEXT-ALIGN: center">Dpto01</TD><TD style="TEXT-ALIGN: center">Robert Magic</TD><TD style="TEXT-ALIGN: center">email@0006</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">Data08</TD><TD style="TEXT-ALIGN: center">591</TD><TD style="TEXT-ALIGN: center">Dpto03</TD><TD style="TEXT-ALIGN: center">Robert Kosinsk</TD><TD style="TEXT-ALIGN: center">email@0008</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">Data12</TD><TD style="TEXT-ALIGN: center">652</TD><TD style="TEXT-ALIGN: center">Dpto02</TD><TD style="TEXT-ALIGN: center">Robert Kosinsk</TD><TD style="TEXT-ALIGN: center">email@0012</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center">Data15</TD><TD style="TEXT-ALIGN: center">754</TD><TD style="TEXT-ALIGN: center">Dpto05</TD><TD style="TEXT-ALIGN: center">Robert Google</TD><TD style="TEXT-ALIGN: center">email@0015</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Criteria</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Robert</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Sheet1

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col01 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col02 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col03 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col04 </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Col05 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">Data03</TD><TD style="TEXT-ALIGN: center">487</TD><TD style="TEXT-ALIGN: center">Dpto03</TD><TD style="TEXT-ALIGN: center">Tina Gold</TD><TD style="TEXT-ALIGN: center">email@0003</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">Data04</TD><TD style="TEXT-ALIGN: center">489</TD><TD style="TEXT-ALIGN: center">Dpto04</TD><TD style="TEXT-ALIGN: center">Paul Paragony</TD><TD style="TEXT-ALIGN: center">email@0004</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">Data05</TD><TD style="TEXT-ALIGN: center">286</TD><TD style="TEXT-ALIGN: center">Dpto05</TD><TD style="TEXT-ALIGN: center">Mark Google</TD><TD style="TEXT-ALIGN: center">email@0005</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">Data07</TD><TD style="TEXT-ALIGN: center">498</TD><TD style="TEXT-ALIGN: center">Dpto02</TD><TD style="TEXT-ALIGN: center">Kristine Gold</TD><TD style="TEXT-ALIGN: center">email@0007</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">Data09</TD><TD style="TEXT-ALIGN: center">487</TD><TD style="TEXT-ALIGN: center">Dpto04</TD><TD style="TEXT-ALIGN: center">Tina Google</TD><TD style="TEXT-ALIGN: center">email@0009</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">Data11</TD><TD style="TEXT-ALIGN: center">513</TD><TD style="TEXT-ALIGN: center">Dpto01</TD><TD style="TEXT-ALIGN: center">Paula Google</TD><TD style="TEXT-ALIGN: center">email@0011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">Data14</TD><TD style="TEXT-ALIGN: center">202</TD><TD style="TEXT-ALIGN: center">Dpto04</TD><TD style="TEXT-ALIGN: center">Karla Gold</TD><TD style="TEXT-ALIGN: center">email@0014</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center">Data15</TD><TD style="TEXT-ALIGN: center">754</TD><TD style="TEXT-ALIGN: center">Dpto05</TD><TD style="TEXT-ALIGN: center">Robert Google</TD><TD style="TEXT-ALIGN: center">email@0015</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Criteria</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Go</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Sheet2

Here is my suggestion of the code for the filter.

Code: 
Sub Testmk()
    Dim MyInput1 As Variant, LastRow As Long
    MyInput1 = InputBox("To which employee are you wishing to address" & _
    " this email? Correctly enter first name and surname DO NOT use Mr etc..", _
    "Enter Employee name", Format(Text, ""))
    MsgBox "Employee selected is " & MyInput1
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    'Autofilter section
    Range("A1:E" & LastRow).AutoFilter Field:=4, Criteria1:="*" & MyInput1 & "*"
End Sub

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Retrieving data by dynamic address
Replies
1
Views
84
NdNoviceHlp
NdNoviceHlp
V
  • Question Question
autofilter
Replies
2
Views
387
vmjan02
V
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
386
Peter_SSs
Peter_SSs
Y
  • Solved
AutoFilter with Dates - Date range, yes; single date, no
Replies
7
Views
374
Alex Blakenburg
A
T
  • Question Question
Range as filter criteria
Replies
5
Views
321
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,590
Messages
6,179,749
Members
452,940
Latest member
rootytrip

Share this page

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
Back
Top