Search From Specified Sheet

User avatar
adam
SilverLounger
Posts: 2069
Joined: 23 Feb 2010, 12:07

Search From Specified Sheet

Post by adam »

Hi anyone,

How could I make the following code to search parameters from the sheet "MySheet" instead of the active sheet?

Code: Select all

Private Sub SearchBtn_Click()

    Dim SearchTerm As String
    Dim SearchColumn As String
    Dim RecordRange As Range
    Dim FirstAddress As String
    Dim FirstCell As Range
    Dim RowCount As Integer
    
    ' Display an error if no search term is entered
    If BillNo.Value = "" And OPNo.Value = "" And RName.Value = "" And ContactNo.Value = "" Then
    
        MsgBox "No search term specified", vbCritical + vbOKOnly
        Exit Sub
    
    End If
    
    ' Work out what is being searched for
    If BillNo.Value <> "" Then
    
        SearchTerm = BillNo.Value
        SearchColumn = "Bill No."
        
    End If
    
    If OPNo.Value <> "" Then
    
        SearchTerm = OPNo.Value
        SearchColumn = "OP Number"
        
    End If

    If RName.Value <> "" Then
    
        SearchTerm = RName.Value
        SearchColumn = "Recipient Name"
        
    End If

    If ContactNo.Value <> "" Then
    
        SearchTerm = ContactNo.Value
        SearchColumn = "Contact No."
        
    End If
    
    Results.Clear
    
        ' Only search in the relevant table column i.e. if somone is searching Location
        ' only search in the Location column
        With Range("Table2[" & SearchColumn & "]")

            ' Find the first match
            Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)

            ' If a match has been found
            If Not RecordRange Is Nothing Then

                FirstAddress = RecordRange.Address
                RowCount = 0

                Do
                
                    ' Set the first cell in the row of the matching value
                    Set FirstCell = Range("A" & RecordRange.Row)
                    
                    ' Add matching record to List Box
                    Results.AddItem
                    Results.List(RowCount, 0) = FirstCell(1, 1)
                    Results.List(RowCount, 1) = FirstCell(1, 2)
                    Results.List(RowCount, 2) = FirstCell(1, 3)
                    Results.List(RowCount, 3) = FirstCell(1, 4)
                    Results.List(RowCount, 4) = FirstCell(1, 5)
                    Results.List(RowCount, 5) = FirstCell(1, 6)
                    Results.List(RowCount, 6) = FirstCell(1, 7)
                    RowCount = RowCount + 1
                    
                    ' Look for next match
                    Set RecordRange = .FindNext(RecordRange)

                    ' When no further matches are found, exit the sub
                    If RecordRange Is Nothing Then

                        Exit Sub

                    End If

                ' Keep looking while unique matches are found
                Loop While RecordRange.Address <> FirstAddress

            Else
            
                ' If you get here, no matches were found
                Results.AddItem
                Results.List(RowCount, 0) = "Nothing Found"
            
            End If

        End With

End Sub
Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 68805
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

There are two occurrences of Range(...) in the code. Change them to Worksheets("MySheet").Range(...)
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2069
Joined: 23 Feb 2010, 12:07

Re: Search From Specified Sheet

Post by adam »

Thanks very much Hans. Much appreciated. I missed that.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2069
Joined: 23 Feb 2010, 12:07

Re: Search From Specified Sheet

Post by adam »

I have another query regarding this code.

When the list-box is populated, the first data which starts from row 2 of the sheet gets populated as the last data row to the list-box. What may be the reason for the this?

In other words, the list box doesn't get populated with data as in order of the worksheet.

Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 68805
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

What happens if you change

Code: Select all

            Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
to

Code: Select all

            Set RecordRange = .Find(What:=SearchTerm, After:=.Cells(.Cells.Count), LookIn:=xlValues)
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2069
Joined: 23 Feb 2010, 12:07

Re: Search From Specified Sheet

Post by adam »

Thanks very much for the help. It worked fine.
Best Regards,
Adam