Searchable Tables - Cannot Search Numbers or Dates

You have three types of search criteria, alphanumeric text, numeric text and dates. The search method is different for each of them. Therefore your code must modify whatever is entered by the user to match the requirement of the filter. The code below does that.

Option Explicit

Sub SearchTable()
' 266 ++ TeachExcel.com

    Dim MainSheet       As Worksheet
    Dim MyTable         As ListObject
    Dim Criterium       As String                      ' item to search for
    Dim FieldName       As String
    Dim Clm             As Variant                      ' field number

    ' set basics
    Set MainSheet = Worksheets("Main")
    With MainSheet
        Set MyTable = .ListObjects("MyTable")
        Criterium = .Range("B3").Value
        FieldName = .Range("D3").Value
    End With

    'Check if there is any user input
    If Criterium <> "" Then                             ' skip if no input
        If IsDate(Criterium) Then
            Criterium = Format(Criterium, "dd/mm/yyyy")
        ElseIf Not IsNumeric(Criterium) Then
            Criterium = "*" & Criterium & "*"
        End If
        With MyTable
            .AutoFilter.ShowAllData                     ' Clear any filters
            Clm = WorksheetFunction.Match(FieldName, .HeaderRowRange, 0)
            .Range.AutoFilter Field:=Clm, Criteria1:=Criterium
        End With
    Else
        MsgBox "Please enter a search criterium in cell B3.", _
               vbExclamation, "No selection made"
    End If
End Sub

My code cuts out all the IFs and reads the field column directly from the table's headers. So, the focus of the code is on modifying the user's entry as opposed to your version of it which uses many lines to identify the column to be filtered. The change may strike you as confusing initially. However, when the chips are down the above procedure does more even though it's shorter. Please find it demonstrated in the attached workbook.

EDIT

For what it's worth, I attach a second workbook which has some changes from the first. For example, the code was moved from the worksheet's code module to a standard code module, which is the normal location of procedures called from a worksheet. But that isn't all.

The main difference is that changing the Field criterium loads a list of available Search criteria into a validation drop-down in B3. And making a selection from B3, immediately sets the filter for that selection.

These additions make your project not only more exciting but also more complex. It will take a lot of additional testing to iron out any possible kinks. I didn't do that. It's yours to play with. Good luck.