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