Search for multiple values and select

Question

So far, I have an excel file as such

https://i.stack.imgur.com/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.

I'm unclear on the concept. Your narrative says you want to search for all matches in the spreadsheet and select them. Your code uses autofilter on a single column. Do you want a non-contiguous range of matches throughout hte worksheet selected or are you trying to filter a column for multiple values? — , Mar 01 '16 at 01:34
I just realised I made a mistake, I was testing with that but obviously I didnt understand what it was meant for, But I do want it to be the whole spreadsheet not just one column — Sean, Mar 01 '16 at 02:35

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

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

Alright, Thank you for the information! This will help me alot — Sean, Mar 01 '16 at 19:39
I have been trying to figure out where and how to add in a predictive mode so that, Say I enter 19 for example, It will automatically round either up or down by a value of 1 (so that if the spreadsheet has 18 or 20, It selects both if I enter 19 but no 19 was found) but I cant figure out how to add it without the use of a `Function Round_Up()` Any ideas or something to help? Much appreciated! — Sean, Mar 02 '16 at 03:29
To do that you would have to look at each cell individually. There is no 'predictive mode' in a [Range.Find method](https://msdn.microsoft.com/en-us/library/office/ff839746.aspx). — , Mar 02 '16 at 03:33
Oh damn, So it wouldnt even be possible if you could work in algorithms to possibly achieve this result? — Sean, Mar 02 '16 at 05:09

Search for multiple values and select

1 Answers1