Implementing a 'tag'-like filter for a column in an Excel table?-CodePudding

I have a column in an excel table that may contain values such as:

5
7,1,8
3,5
ALL

I want to implement a macro that can filter for these values, such that if I filter for "5", the first and third results come up. Also, I included the ALL in the list because there may be text tags added in the future. Here's what I have so far, but it gets inconsistent results:

Sub ContainsFilter()
    Dim strName As String
    strName = InputBox("What would you like to search for?")
    If strName = "" Then
    ActiveSheet.ShowAllData
    Else
    ActiveSheet.ListObjects("Table").Range.AutoFilter Field:=7, Criteria1:="=*" & strName & "*", Operator:=xlAnd
    End If
End Sub

CodePudding user response：

The following should work:

Option Explicit

Public Sub ContainsFilter()
    Dim InputReturn As Variant
    InputReturn = Application.InputBox(Prompt:="What would you like to search for?", Title:="Filter Field 7", Type:=2) ' type 2 = string
    
    If VarType(InputReturn) = vbBoolean And InputReturn = False Then
        ' User pressed cancel button or X in the right corner
        Exit Sub
    End If
    
    If InputReturn = vbNullString Then
        ActiveSheet.ShowAllData
    Else
        ActiveSheet.ListObjects("Table").Range.AutoFilter Field:=7, Criteria1:=Array("*" & InputReturn & "*", InputReturn), Operator:=xlFilterValues
    End If
End Sub