Excel VBA AutoFiltering using InputBox to specify the Criteria - *contains*

dougmarkham

Active Member
Joined
Jul 19, 2016
Messages
252
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

My goal is to be able to filter a column in a dynamic table for strings that contain non-alpha characters like #, $, etc using wildcards (*).
I wish to use an InputBox to specify the special character.

Something unexpected is happening:
If I directly code 'Criteria1' e.g., for # i.e.,
VBA Code:
Criteria1:="*#*"
then the below code works.
But
If I use a user InputBox so y = "*#*" and then state
VBA Code:
Criteria1:=y
, then the macro hides all lines.
This despite confirming via
VBA Code:
MsgBox y
that the text strings are identical!

Here is the VBA code I'm using:

VBA Code:
Sub AutoFilter_By_InputBox()

Dim lo      As ListObject
Dim iCol    As Long
Dim x       As String: x = Application.InputBox(Prompt:="Please select character to filter by", Title:="Select Filter Character", Type:=2)
Dim y       As String

y = Chr(34) & Chr(42) & x & Chr(42) & Chr(34)
MsgBox y

  'Set reference to the first Table on the sheet
  Set lo = Sheets("DX_Invoice").ListObjects("InvoiceTable")

  'Set filter field
  iCol = lo.ListColumns("Customer Reference").Index
  
  'Clear Filters
  lo.AutoFilter.ShowAllData

  'All lines starting with .AutoFilter are a continuation
  'of the with statement.
  With lo.Range
    'Contains - wrap search text in asterisks
    .AutoFilter Field:=iCol, Criteria1:=y
  End With

End Sub

Images:
using explicit.PNG


versus

as y.PNG



Would anyone be willing to help me work out how to get this filter to work with InputBox?

Kind regards,

Doug.

P.S.
Input box shows correct criteria

confirming inputbox.PNG
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Why not just use
VBA Code:
    .AutoFilter Field:=iCol, Criteria1:="*" & x & "*"
 
Upvote 0
You don't want the quote marks as an actual part of the filter criteria. You should use:

Code:
y = "*" & x & "*"
 
Upvote 0
Why not just use
VBA Code:
    .AutoFilter Field:=iCol, Criteria1:="*" & x & "*"

Hi Fluff,

Awesome! Thanks for your reply!
That works for me: curious how Excel VBA synatax works?!

I've now managed to filter for special characters too via your method: ~, *, and ?, so thanks :)

Working code
VBA Code:
Sub AutoFilter_By_InputBox()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

Dim lo      As ListObject
Dim iCol    As Long
Dim x       As String: x = Application.InputBox(Prompt:="Please select character to filter by", Title:="Select Filter Character", Type:=2)


'Set reference to the first Table on the sheet
Set lo = Sheets("DX_Invoice").ListObjects("InvoiceTable")

'Set filter field
iCol = lo.ListColumns("Customer Reference").Index
  
'Clear Filters
lo.AutoFilter.ShowAllData

If x = "~" Then
    With lo.Range
        .AutoFilter Field:=iCol, Criteria1:="*" & x & x & "*"
    End With
ElseIf x = "?" Then
    With lo.Range
        .AutoFilter Field:=iCol, Criteria1:="*" & "~" & x & "*"
    End With
ElseIf x = "*" Then
    With lo.Range
        .AutoFilter Field:=iCol, Criteria1:="*" & "~" & x & "*"
    End With
Else
    With lo.Range
        .AutoFilter Field:=iCol, Criteria1:="*" & x & "*"
    End With
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic

End Sub

Kind regards,

Doug
 
Upvote 0
You don't want the quote marks as an actual part of the filter criteria. You should use:

Code:
y = "*" & x & "*"

Hi Rory,

Also awesome! Thanks for your alternative to Fluff's reply!
Your answer also worked for me, thanks very much for your time and knowledge!

Kind regards,

Doug.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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