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

[dougmarkham]

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.,

Criteria1:="*#*"

then the below code works.
But
If I use a user InputBox so y = "*#*" and then state

Criteria1:=y

, then the macro hides all lines.
This despite confirming via

MsgBox y

that the text strings are identical!

Here is the VBA code I'm using:

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:

versus

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

 

[Fluff]

Why not just use

    .AutoFilter Field:=iCol, Criteria1:="*" & x & "*"

 

[RoryA]

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

y = "*" & x & "*"

​

 

[dougmarkham]

Why not just use

    .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

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]

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

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]

Glad we could help & thanks for the feedback.