VBA Userform Find Next button


New Member
Feb 19, 2019
Hi all,
My database has multiple columns as follows
Column A: Tracking number (txttracking)
Column B: New / Resupply (txtrequest)
Column C: Resupply No. (txtresupplyno)

e.g. I have 3 rows with the same tracking number
ABC123 new n/a
ABC123 resupply 1
ABC123 resupply 2

Now I have a list and I would like to create a userform with search function (search by tracking number). I was able to do the search function but it only show up the first entry with the matching criteria (i.e. only show ABC123 new, but not resupply 1 and 2).

I'm a new guy here so it would be great if someone can show me a code for the 'find next' button so that when i press 'Search' button the form will show ABC123 new, then click 'find next' to show ABC123 resupply 1, then click 'find next' again to show ABC123 resupply 2?

My current code for search button is as below:

Private Sub search_Click()

'Transferring values from the search box to the controls
Dim FindRow
Dim i As Integer
Dim cRow As String

Set FindRow = nwb.Sheets("Master Database").Range("A:A").Find(What:=cRow, LookIn:=xlValues)
Me.txtCurrentAddress = FindRow.Address
Me.txtCurrentAddress.Visible = False

'error block
On Error GoTo errHandler:

'find the row with the data
cRow = txttracking.Text

If txttracking.Text <> "" Then

Set FindRow = nwb.Sheets("Master Database").Range("A:A").Find(What:=cRow, LookIn:=xlValues)

'add the values to the userform
txttracking.Text = FindRow
txtrequest.Text = FindRow.Offset(0, 1)
txtresupplyno.Text = FindRow.Offset(0, 2)

End If

'error block
On Error GoTo 0
Exit Sub
MsgBox "Record Not Found. "
End Sub

Thanks a lot!

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)


Well-known Member
Jul 3, 2012
Office Version
  1. 2019
  1. Windows
welcome to Forum.

Try this update to your code

Rich (BB code):
Dim FirstAddress As String, strSearch As String
Dim FoundRecord As Range
Dim RecordNo As Long
Const SearchCol As Long = 1

Private Sub Search_Click()
    Dim MatchCount As Integer
'value to search
   strSearch = txttracking.Text
'nothing entered
   If Len(strSearch) = 0 Then Exit Sub
    With nwb.Sheets("Master Database")
'set start range
        If FoundRecord Is Nothing Then Set FoundRecord = .Cells(1, SearchCol)
'search range for strSearch match
        Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlWhole, LookIn:=xlValues)
        If Not FoundRecord Is Nothing Then
'count number matches in range
        MatchCount = Application.CountIf(.Columns(SearchCol), strSearch)
            If FirstAddress <> FoundRecord.Address Then
                RecordNo = RecordNo + 1
'update caption
                Me.Caption = "Search Match " & RecordNo & " of " & MatchCount
'add the values to the userform
                txttracking.Text = FoundRecord.Value
                txtrequest.Text = FoundRecord.Offset(0, 1)
                txtresupplyno.Text = FoundRecord.Offset(0, 2)
'if more than one match in range change Search Button caption
                If MatchCount > 1 Then Me.Search.Caption = "Find Next"

'mark first matched record address
                If Len(FirstAddress) = 0 Then FirstAddress = FoundRecord.Address
'no more matches
                MsgBox strSearch & Chr(10) & Space(20) & Chr(10) & "End Of File", 48, "End Of File"
're-set variables
                RecordNo = 0: Set FoundRecord = Nothing: Me.Search.Caption = "Find": FirstAddress = ""
            End If
'no match found
            MsgBox strSearch & Chr(10) & " Record Not Found", 48, "Not Found"
        End If
    End With
End Sub

If more than one match exists in the search range, the Find button Caption changes to "Find Next" to allow you to continue searching.
Msgbox will be displayed at end of search

I assume your object variable nwb has been initialized elsewhere in your project


Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics