Search Multiple tables on multiple worksheets - find next item.

[Icesurf3r]

Morning All,


I have a search option on my Userform enabling the user to search via Client or Opportunity name, and use the below code to find the result and populate various text/comboboxes on the search form. This enables the user to check it is the correct record before selecting it:

Sub SearchWorksheet()
Dim Reference As String
Reference = UserForm1.SearchOptions.Value
Dim aCell As Range
Dim tbl As ListObject
Dim ws As Worksheet






Select Case UserForm1.SearchBy.Value
    Case "Opportunity Name"
        For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            Set aCell = tbl.ListColumns("Opportunity Name").DataBodyRange.Find(What:=Reference, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
            If Not aCell Is Nothing Then
            'Determines which table the result was found on
            Select Case tbl.Name
            'selects the correct offset for the information
            'depending on which table the match was found on
                Case "Table_Main"
                  UserForm1.SearchDate.Value = aCell.Offset(0, -7)
                  UserForm1.SearchRefNum.Value = aCell.Offset(0, -6)
                  UserForm1.SearchLot.Value = aCell.Offset(0, -5)
                  UserForm1.SearchDiscipline.Value = aCell.Offset(0, -4)
                  UserForm1.SearchLead.Value = aCell.Offset(0, -3)
                  UserForm1.SearchLead2.Value = aCell.Offset(0, -2)
                  UserForm1.SearchOpptype.Value = aCell.Offset(0, -1)
                  UserForm1.SearchOppname.Value = aCell
                  UserForm1.SearchClient.Value = aCell.Offset(0, 1)
                  UserForm1.SearchDecision.Value = aCell.Offset(0, 3)
                Case "Table_DirectAward"
                  UserForm1.SearchDate.Value = aCell.Offset(0, -7)
                  UserForm1.SearchRefNum.Value = aCell.Offset(0, -6)
                  UserForm1.SearchLot.Value = aCell.Offset(0, -5)
                  UserForm1.SearchDiscipline.Value = aCell.Offset(0, -4)
                  UserForm1.SearchLead.Value = aCell.Offset(0, -3)
                  UserForm1.SearchLead2.Value = aCell.Offset(0, -2)
                  UserForm1.SearchOpptype.Value = aCell.Offset(0, -1)
                  UserForm1.SearchOppname.Value = aCell
                  UserForm1.SearchClient.Value = aCell.Offset(0, 1)
                Case Else
                  UserForm1.SearchDate.Value = aCell.Offset(0, -7)
                  UserForm1.SearchRefNum.Value = aCell.Offset(0, -6)
                  UserForm1.SearchLot.Value = aCell.Offset(0, -5)
                  UserForm1.SearchDiscipline.Value = aCell.Offset(0, -4)
                  UserForm1.SearchLead.Value = aCell.Offset(0, -3)
                  UserForm1.SearchLead2.Value = aCell.Offset(0, -2)
                  UserForm1.SearchOpptype.Value = aCell.Offset(0, -1)
                  UserForm1.SearchOppname.Value = aCell
                  UserForm1.SearchClient.Value = aCell.Offset(0, 1)
                  UserForm1.SearchDecision.Value = aCell.Offset(0, 2)
            End Select
            End If
            
        Next tbl
        Next ws
    Case "Client"
    
    MsgBox "Searching by Client Name", vbOKOnly
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            Set aCell = tbl.ListColumns("Client").DataBodyRange.Find(What:=Reference, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
            If Not aCell Is Nothing Then
            'Determines which table the result was found on
            Select Case tbl.Name
            'selects the correct offset for the information
            'depending on which table the match was found on
                Case "Table_Main"
                  UserForm1.SearchDate.Value = aCell.Offset(0, -8)
                  UserForm1.SearchRefNum.Value = aCell.Offset(0, -7)
                  UserForm1.SearchLot.Value = aCell.Offset(0, -6)
                  UserForm1.SearchDiscipline.Value = aCell.Offset(0, -5)
                  UserForm1.SearchLead.Value = aCell.Offset(0, -4)
                  UserForm1.SearchLead2.Value = aCell.Offset(0, -3)
                  UserForm1.SearchOpptype.Value = aCell.Offset(0, -2)
                  UserForm1.SearchOppname.Value = aCell.Offset(0, -1)
                  UserForm1.SearchClient.Value = aCell
                  UserForm1.SearchDecision.Value = aCell.Offset(0, 2)
                Case "Table_DirectAward"
                  UserForm1.SearchDate.Value = aCell.Offset(0, -8)
                  UserForm1.SearchRefNum.Value = aCell.Offset(0, -7)
                  UserForm1.SearchLot.Value = aCell.Offset(0, -6)
                  UserForm1.SearchDiscipline.Value = aCell.Offset(0, -5)
                  UserForm1.SearchLead.Value = aCell.Offset(0, -4)
                  UserForm1.SearchLead2.Value = aCell.Offset(0, -3)
                  UserForm1.SearchOpptype.Value = aCell.Offset(0, -2)
                  UserForm1.SearchOppname.Value = aCell.Offset(0, -1)
                  UserForm1.SearchClient.Value = aCell
                Case Else
                  UserForm1.SearchDate.Value = aCell.Offset(0, -8)
                  UserForm1.SearchRefNum.Value = aCell.Offset(0, -7)
                  UserForm1.SearchLot.Value = aCell.Offset(0, -6)
                  UserForm1.SearchDiscipline.Value = aCell.Offset(0, -5)
                  UserForm1.SearchLead.Value = aCell.Offset(0, -4)
                  UserForm1.SearchLead2.Value = aCell.Offset(0, -3)
                  UserForm1.SearchOpptype.Value = aCell.Offset(0, -2)
                  UserForm1.SearchOppname.Value = aCell.Offset(0, -1)
                  UserForm1.SearchClient.Value = aCell.Offset
                  UserForm1.SearchDecision.Value = aCell.Offset(0, 1)
            End Select
            End If
            
        Next tbl
        Next ws
End Select

This works well when searching by Opportunity Name as this is a unique value. However, when searching by Client there could be multiple results so I need a way of moving to the next or previous record (Just in case the user gets click happy). To facilitate this, I have included a Next and Previous button on the search form - I'm just not sure how to code the functions to get them to work, or if I need to change my main search code above to get this all to work.


So any help you can give would be greatly appreciated as this is the last piece of the puzzle to get my Userform up and running within the business.