Hi,
I found the code below the problem is can only find if type the SAGEID but want to be able to search for other fields listed in the code if possible to search wildcard the SearchForm have five textboxes to search for for example if just want to find JS Analyst when type for that list all of them (attaching picture of the form).
here is the code:
Thank you,
I found the code below the problem is can only find if type the SAGEID but want to be able to search for other fields listed in the code if possible to search wildcard the SearchForm have five textboxes to search for for example if just want to find JS Analyst when type for that list all of them (attaching picture of the form).
here is the code:
VBA Code:
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 SAGEID.Value = "" And VENDOR.Value = "" And GL_ACCOUNT.Value = "" And OrgUnit.Value = "" And Analyst.Value = "" Then
MsgBox "No search term specified", vbCritical + vbOKOnly
Exit Sub
End If
' Work out what is being searched for
If SAGEID.Value <> "" Then
SearchTerm = SAGEID.Value
SearchColumn = "SAGEID"
End If
If VENDOR.Value <> "" Then
SearchTerm = VENDOR.Value
SearchColumn = "VENDOR"
End If
If OrgUnit.Value <> "" Then
SearchTerm = GL_ACCOUNT.Value
SearchColumn = "G/L ACCOUNT"
End If
If OrgUnit.Value <> "" Then
SearchTerm = OrgUnit.Value
SearchColumn = "ORG UNIT"
If Analyst.Value <> "" Then
SearchTerm = Analyst.Value
SearchColumn = "Analyst"
End If
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("Table1[" & 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)
Results.List(RowCount, 7) = FirstCell(1, 8)
Results.List(RowCount, 8) = FirstCell(1, 9)
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
Thank you,