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.
 

Forum statistics

Threads
1,078,442
Messages
5,340,305
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top