Search Multiple tables on multiple worksheets - find next item.

Icesurf3r

New Member
Joined
Feb 13, 2013
Messages
39
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:


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

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,095,205
Messages
5,443,058
Members
405,214
Latest member
Gautham A

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top