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

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
223
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
Why not just use
VBA Code:
    .AutoFilter Field:=iCol, Criteria1:="*" & x & "*"
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,650
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You don't want the quote marks as an actual part of the filter criteria. You should use:

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

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
223
Office Version
  1. 365
Platform
  1. Windows
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
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
223
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,490
Members
410,686
Latest member
Fer9us
Top