Hi Everyone, I've created a listbox search function in VBA that will list products based on the assigned description in my database. My current code will work for only consecutive words, but I would like to be able to generate results regardless of order.
For example, the current code will return me "Red Pair of Tube Socks" if I search for "Red Pair" or "of Tube S". I'd like to be able to arrive at that result if I search for "Red Socks" or "Tube Red".
Does anyone have any advice how to do this? This is my current code:
For example, the current code will return me "Red Pair of Tube Socks" if I search for "Red Pair" or "of Tube S". I'd like to be able to arrive at that result if I search for "Red Socks" or "Tube Red".
Does anyone have any advice how to do this? This is my current code:
VBA Code:
Private Sub TextBox1_Change()
Me.TextBox1 = Format(StrConv(Me.TextBox1, vbLowerCase))
Dim sh As Worksheet
Set sh = Sheets("ProductDatabase")
Dim i As Long
Dim x As Long
Dim p As Long
Me.ListBox1.Clear
'FOR LISTBOX HEADER
Me.ListBox1.AddItem "Item Number"
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "Description"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Unit of Measure"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Vendor"
Me.ListBox1.Selected(0) = True
For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 2))
p = Me.TextBox1.TextLength
If LCase(Mid(sh.Cells(i, 2), x, p)) = Me.TextBox1 And Me.TextBox1 <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 1)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 2)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 3)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 4)
End With
End If
Next x
Next i
End Sub