Hi,
I am using the following code to search a database of information and then display it on a different sheet.
The user types the search term into a textbox and then presses a command button to search the database.
Currently it only searches for an exact match. How can i adapt it so it searches for similar strings?
Thanks
Code
Sub SearchDatabase()
Dim rRange As Range
Dim rCell As Range
Dim ResultsOffset As Integer
Dim ResultsRange As Range
On Error Resume Next
Set ResultsRange = Sheet2.Range("H13", Sheet2.Range("M65536").End(xlUp))
ResultsRange.ClearContents
Set rRange = Sheet1.Range("A1", Sheet1.Range("F65536").End(xlUp))
ResultsOffset = 13
For Each rCell In rRange
If rCell = Sheet2.TextBox1 Then
Sheet2.Range("H" & ResultsOffset) = Sheet1.Cells(rCell.Row, 1)
Sheet2.Range("I" & ResultsOffset) = Sheet1.Cells(rCell.Row, 2)
Sheet2.Range("J" & ResultsOffset) = Sheet1.Cells(rCell.Row, 3)
Sheet2.Range("K" & ResultsOffset) = Sheet1.Cells(rCell.Row, 4)
Sheet2.Range("L" & ResultsOffset) = Sheet1.Cells(rCell.Row, 5)
Sheet2.Range("M" & ResultsOffset) = Sheet1.Cells(rCell.Row, 6)
ResultsOffset = ResultsOffset + 1
End If
Next rCell
ResultsOffset = ResultsOffset - 13
If ResultsOffset = 0 Then
MsgBox "No Match Found"
Else
If ResultsOffset = 1 Then
MsgBox ResultsOffset & " entry found."
Else
MsgBox ResultsOffset & " entries found."
End If
End If
End Sub
Display More