Dim SearchTerm as String
Dim dataRange as Range ,OneCell as Range
With ThisWorkbook.Sheets(1).Range("A:A")
Set dataRange = Range(.Cells(1,1), .Cells(.Rows.Count,1).End(xlup))
End With
SearchTerm = TextBox1.Text
For Each oneCell in dataRange
If CStr(oneCell.Value) = searchTerm Then
With ListBox1
.AddItem searchTerm
.List(.ListCount - 1, 1) = CStr(oneCell.Offset(0,1).Value)
End With
Next oneCell
'...
For Each oneCell in dataRange
If CStr(oneCell.Value) Like "*" & searchTerm & "*" Then
With ListBox1
.AddItem searchTerm
.List(.ListCount - 1, 1) = .Address(False,False,xlA1,True)
End With
Next oneCell
.AddItem CStr(oneCell.Value)
.List(.ListCount - 1, 1) = oneCell.Address(False,False,xlA1,True)