Userform to display values from multiple rows

Question

I created a userform to enable users to input up to 5 unique IDs in text boxes as well as 6 different pieces of information (a mixture of text boxes and list boxes) that remain constant for all 5 IDs input.

This information is then logged on to a worksheet, with each unique ID having its own row and the remaining 6 pieces of information being duplicated in each corresponding row.

For every logged row of IDs a unique reference no. will be automatically generated, meaning that you will have a table in which the first two columns look as follows:

Unique Reference Number	Unique ID
001	2120
001	2130
001	8765
002	7688
002	7684
002	7682
002	7681
002	7666

I added a command button and text box to the userform to enable users to search for a unique reference number (e.g. 001), but I would then like the code to find all the corresponding rows (up to 5) in a spreadsheet containing the searched for reference number and then display the up to 5 rows and 6 pieces of information in the same text/list boxes that were used to log the information within the userform.

When I currently search the reference number the userform displays the first ID in the first ID text box as well as the 6 pieces of information in their corresponding text boxes with no issues. But it is then displaying the ID numbers for all subsequent rows in the second ID text box - meaning that it is finding the correct information but not displaying it into the correct text box in the userform.

Essentially, I am trying to get the code to loop through the first column in the worksheet and find all matching values (Ref numbers) and then retrieve and display the unique ID information from each row in the corresponding ID text boxes in the userform.

Private Sub CommandButton1_Click()

    Dim x As Long
    Dim y As Long
    Dim found As Boolean 

    With Sheets("Example Spreadsheet")
        x = .Range("A" & .Rows.Count).End(xlUp).Row
        For y = 1 To x
            If .Cells(y, 1).Text = Search.Value Then
                If Not found Then 
                    found = True 
                    Me.ID1.Value = .Cells(y, 2)
                    Me.Branch.Value = .Cells(y, 3)
                    Me.AccountNo.Value = .Cells(y, 4)
                    Me.Name.Value = .Cells(y, 5)
                    Me.DateReceived.Value = .Cells(y, 6)
                    Me.DateClosed.Value = .Cells(y, 7)
                Else 
                    Me.ID2.Value = Me.ID2.Value & .Cells(y, 2)
                End If
            End If
        Next y
    End With

End Sub

The code only refers to text boxes ID1 and ID2 but I played around incorporating the other ID3-5 text boxes and can't get it to display information correctly.

Not sure I follow that. Is it significant that once found becomes True it is never reset to False? Not sure you need that Boolean at all, and probably more efficient to use Find. — SJR
Thank you for your comments. Yeah I too wasn't sure about the need for the Boolean - currently learning as I go. Will certainly consider the Find function. — robbo21

Tim Williams Tim Williams · Accepted Answer · 2021-01-20T17:58:33

When doing something involving finding matches I like to separate out the two parts - first find all the matches and then process them. Keeps your code cleaner and you can focus on the main task instead of context-switching between finding and processing. (untested code below)

Private Sub CommandButton1_Click()
    Const MAX_HITS As Long = 5
    Dim x As Long, found As Collection, rw As Range

    'get all matches
    With Sheets("Example Spreadsheet")
        Set found = FindAll(.Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row), Search.Value)
    End With
    
    If found.Count > 0 Then
        For x = 1 To found.Count
            If x = 1 Then
                Set rw = found(x).EntireRow 'the whole row for this matched cell
                Me.ID1.Value = rw.Cells(2).Value
                Me.Branch.Value = rw.Cells(3).Value
                Me.AccountNo.Value = rw.Cells(4).Value
                Me.Name.Value = rw.Cells(5).Value
                Me.DateReceived.Value = rw.Cells(6).Value
                Me.DateClosed.Value = rw.Cells(7).Value
            ElseIf x > MAX_HITS Then
                'make sure we didn't find too many...
                MsgBox "Too many matches (" & found.Count & ") for " & Search.Value
            Else
                Me.Controls("ID" & x).Value = found(x).Value 'refer to control by name
            End If
        Next x
    Else
        MsgBox "No hits for " & Search.Value
    End If
    
End Sub

'return all matching cells in a collection
Public Function FindAll(rng As Range, v) As Collection
    Dim rv As New Collection, f As Range
    Dim addr As String
 
    Set f = rng.Find(what:=v, after:=rng.Cells(rng.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If Not f Is Nothing Then addr = f.Address()
    Do Until f Is Nothing
        rv.Add f
        Set f = rng.FindNext(after:=f)
        If f.Address() = addr Then Exit Do
    Loop
    Set FindAll = rv
End Function

Userform to display values from multiple rows

1 Answers