Display the closest results in list box

[yazan]

Hello guys,
i have this code to search for textbox1 value in sheet1, but this code is only showing 100% matched value
my problem is : i want if textbox1.value = "jo" then
listbox1 results are each cell contains "jo"
any solution ?
_________________________________________________________________________
Dim r As Long
For r = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
If Trim(Me.TextBox1.Value) = Trim(Sheet1.Cells(r, 1).Value) Then
With Me.ListBox1
.AddItem
.List(.ListCount - 1, 3) = Sheet1.Cells(r, 1).Value
.List(.ListCount - 1, 2) = Sheet1.Cells(r, 56).Value
.List(.ListCount - 1, 1) = Sheet1.Cells(r, 9).Value
End With
End If
Next r
_________________________________________________________________________

 

[mikerickson]

Dim SearchTerm as String
SearchTerm = LCase(Me.TextBox1.Value)
For r = 2 to Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    If LCase(CStr(Sheet1.Cells(r, 1).Value)) Like ("*" & searchTerm "*") Then
        ' etc
    End If
Next t

Omit the second asterisk if you want only words that start with "jo"

 

[yazan]

Dim SearchTerm as String
SearchTerm = LCase(Me.TextBox1.Value)
For r = 2 to Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    If LCase(CStr(Sheet1.Cells(r, 1).Value)) Like ("*" & searchTerm "*") Then
        ' etc
    End If
Next t

Omit the second asterisk if you want only words that start with "jo"

thank you but i want if it's contain jo not only if its start with it