Macro to Filter by Number

[ghp2017]

For some I can get it to search by text but when I try a number it filters everything out like theres no match.

Dim strName As String
strName = InputBox("What number would you like to search for?")
Selection.AutoFilter Field:=4, Criteria1:="=*" & strName & "*", Operator:=xlAnd

 

[ExcelToDAX]

Have you tried changing the StrName from a String to Long?

 

[Joe4]

Are you able to manually create a filter to search for the number you want, and have it work the way you want?
If so, then turn on your Macro Recorder and record yourself manually creating that filter.
Then stop the Macro Recorder, and take a look at the code you just recorded. That is what you need to build your VBA code to look like.

 

[DanteAmor]

If you are going to filter only numbers.
Assuming you have the headers in row1 and want to filter on column D, try the following:

Sub testfilter()
  Dim rng As Range, c As Range
  Dim strName As String
  Dim dic As Object
  Application.ScreenUpdating = False
  
  strName = InputBox("What number would you like to search for?")
  
  Set dic = CreateObject("Scripting.Dictionary")
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  Set rng = Range("A1", Range("D" & Rows.Count).End(3))
  
  For Each c In rng.Columns(4).Cells
    If InStr(1, c.Text, strName) > 0 Then
      dic(c.Text) = Empty
    End If
  Next
  If dic.Count > 0 Then
    rng.AutoFilter 4, dic.keys, xlFilterValues
  Else
    MsgBox "There is no data to filter"
  End If
  
  Application.ScreenUpdating = True
End Sub

It even works with texts too.

 

[ghp2017]

If you are going to filter only numbers.
Assuming you have the headers in row1 and want to filter on column D, try the following:

Sub testfilter()
  Dim rng As Range, c As Range
  Dim strName As String
  Dim dic As Object
  Application.ScreenUpdating = False
 
  strName = InputBox("What number would you like to search for?")
 
  Set dic = CreateObject("Scripting.Dictionary")
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  Set rng = Range("A1", Range("D" & Rows.Count).End(3))
 
  For Each c In rng.Columns(4).Cells
    If InStr(1, c.Text, strName) > 0 Then
      dic(c.Text) = Empty
    End If
  Next
  If dic.Count > 0 Then
    rng.AutoFilter 4, dic.keys, xlFilterValues
  Else
    MsgBox "There is no data to filter"
  End If
 
  Application.ScreenUpdating = True
End Sub

It even works with texts too.

Thanks!!