VBA Userform Find Next button

dorsetcream

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

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:


Code:
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
errHandler:
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,)

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,085
Office Version
  1. 2019
Platform
  1. Windows
Hi,
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
                
            Else
'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
        Else
'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

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,108,956
Messages
5,525,883
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top