Search for multiple values and select

Question

So far, I have an excel file as such

https://i.sstatic.net/zX3xC.png

My problem is that I want to be able to input a number after having the search button pressed and an Input box appears, With the number in the search bar for all numbers that match in the spreadsheet to be selected.

Also as as addition to be able to put in a few numbers (40, 21, 33 separated by commas)

My current code is:

Sub SEARCH_Click()
    Dim sh1 As Sheet1
    Dim rng As Range
    Dim uname As String

    Set sh1 = Sheet1: uname = InputBox("Input")
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    With sh1
        .AutoFilterMode = False
        Set rng = .Range("A4", .Range("A" & .Rows.Count).End(xlUp))
        On Error Resume Next
        rng.SpecialCells(xlCellTypeVisible).Select
        If Err.number <> 0 Then MsgBox "Data not found" _
            Else MsgBox "All matching data has been selected"
        .AutoFilterMode = False
        On Error GoTo 0
    End With

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

I am fairly new to coding so a lot of this has come from internet research etc.

Answer 1

Abandon your AutoFilter method in favor of a Range.Find method. While ultimately possible with a series of .AutoFilters applied to each column, simply collecting the results from a .Find operation with the Union method makes more sense.

Private Sub CommandButton1_Click()
    Dim uname As String, sh1 As Worksheet   '<~~ there is no var type called Sheet1
    Dim v As Long, fnd As Range, rng As Range, addr As String, vals As Variant
    
    Set sh1 = Sheet4
    
    uname = InputBox("Search for...")
    vals = Split(Replace(uname, Chr(32), vbNullString) & Chr(44), Chr(44))
    ReDim Preserve vals(UBound(vals) - 1)
    
    With sh1
        For v = LBound(vals) To UBound(vals)
            If IsNumeric(vals(v)) Then vals(v) = Val(vals(v))
            Set fnd = .Cells.Find(What:=vals(v), LookIn:=xlValues, LookAt:=xlWhole, _
                                  SearchOrder:=xlByRows, SearchFormat:=False)
            If Not fnd Is Nothing Then
                addr = fnd.Address
                Do
                    If rng Is Nothing Then
                        Set rng = fnd
                    Else
                        Set rng = Union(rng, fnd)
                    End If
                    Set fnd = .Cells.FindNext(after:=fnd)
                Loop Until addr = fnd.Address
            End If
            addr = vbNullString
            Set fnd = Nothing
        Next v
        If Not rng Is Nothing Then rng.Select
    End With
    
End Sub

It is not clear what actions you want to perform after the Range .Select¹ method has been applied. I would suggest that a simple With ... End With statement woudl allow you to continue working on the rng discontiguous Range object without actually selecting it at all.

    

---

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}