VBA- Using search message box

[Donfal]

Good morning, I have a little issue with the below code I am trying to figure out.
The function works correctly it opens a search window it finds the desired value on sheet2 but only returns 1 result regardless of how many instances of that number appear on sheet2. I would like it to show the multiple rows on the message box. currently it shows just the first instance. Included is the code used for the search and also a screen shot showing the value in multiple rows.

Private Sub CommandButton2_Click()
    Dim Search As Variant
    Dim msg As String
    Dim c As Range, Rng As Range
    Dim wsOutPut As Worksheet, sh As Worksheet


    Set wsOutPut = Worksheets("Sheet2")
    'Open inputbox
Top:
    msg = ""
    Do
        Search = InputBox("Enter Search Number Value:", "Search")
        If StrPtr(Search) = 0 Then Exit Sub
    Loop Until IsNumeric(Search)


    Application.ScreenUpdating = False




    For Each sh In Worksheets(Array("Sheet2"))
        With sh
            .Activate
            Set c = .Columns(6).Find(What:=CLng(Search), _
                                     LookIn:=xlValues, _
                                     LookAt:=xlWhole, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlNext, _
                                     MatchCase:=True, _
                                     SearchFormat:=False)
            If Not c Is Nothing Then
                Set Rng = .Rows(c.Row)
                
                
                msg = msg & "Serial number found on row " & c.Row & Chr(10) & Chr(10)
            Else
                msg = msg & "Serial number not found!" & Chr(10) & Chr(10)
            End If
        End With
    Next sh


    Application.ScreenUpdating = True
    msg = MsgBox(msg & Chr(10) & "Do you want to make another search?", 36, "Results")
    If msg = 6 Then GoTo Top
End Sub

Thank you

 

[dmt32]

Hi welcome to forum

To understand your intention with the code, you have included a For Next Loop to presumably search other worksheets yet to to be placed in the array?

For Each sh In Worksheets(Array("Sheet2"))

or do you intend the code to just Search Sheet2?

Dave

 

[Donfal]

Hi welcome to forum

To understand your intention with the code, you have included a For Next Loop to presumably search other worksheets yet to to be placed in the array?

For Each sh In Worksheets(Array("Sheet2"))

or do you intend the code to just Search Sheet2?

Dave

Well the goal is to only search the one sheet yes, I see that this might be a redundant or useless line.

 

[dmt32]

Well the goal is to only search the one sheet yes, I see that this might be a redundant or useless line.

then in that case, try this update to your code & see if does what you want

Sub CommandButton2_Click()
    Dim Search          As Variant
    Dim c               As Range
    Dim sh              As Worksheet
    Dim Response        As VbMsgBoxResult
    
    Dim msg             As String, FirstAddress As String
    Dim Prompts(1 To 2) As String, Prompt As String
    
    Prompts(1) = "Serial number found On row(s) " & Chr(10) & Chr(10)
    Prompts(2) = "Serial number Not found" & Chr(10) & Chr(10)
    
    Set sh = ThisWorkbook.Worksheets("Sheet2")
    
    Do
        'display inputbox
        Do
            Search = InputBox("Enter Search Number Value:", "Search")
            'cancel pressed
            If StrPtr(Search) = 0 Then Exit Sub
        Loop Until IsNumeric(Search)
        
        Set c = sh.Columns(6).Find(What:=CLng(Search), LookIn:=xlValues, LookAt:=xlWhole, _
                                  SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                  MatchCase:=True, SearchFormat:=False)
        If Not c Is Nothing Then
            FirstAddress = c.Address
            msg = Prompts(1)
            Do
                msg = msg & c.Row & Chr(10)
                Set c = sh.Columns(6).FindNext(c)
                If c Is Nothing Then Exit Do
            Loop Until FirstAddress = c.Address
        Else
            msg = Prompts(2) & Search & Chr(10)
        End If
        
        Response = MsgBox(msg & Chr(10) & "Do you want To make another search?", 36, "Results")
        msg = ""
    Loop Until Response = vbNo
    
End Sub

Dave