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