Carly Carlyle Adams Cnf
asked on
VBA Wildcard
Hi, I am new to VBA and coding.
I have a textbox that if I type in text it looks up cells from 4 columns and if there is a match on the first word then it shows up in a list box.
It works but I want to add wildcard so it will look at the entire cell for words that match
ie: green apple slice in cell A1
I want to type apple or slice and it will get sell A1 displaying green apple slice it my list box. can someone please help?
I have a textbox that if I type in text it looks up cells from 4 columns and if there is a match on the first word then it shows up in a list box.
It works but I want to add wildcard so it will look at the entire cell for words that match
ie: green apple slice in cell A1
I want to type apple or slice and it will get sell A1 displaying green apple slice it my list box. can someone please help?
Private Sub UserForm_Initialize()
TextBox1.SetFocus
End Sub
Private Sub TextBox1_Change()
TextBox1.Text = StrConv(TextBox1.Text, vbProperCase)
TextBox1.SetFocus
Dim i As Long
ListBox1.Clear
On Error Resume Next
For i = 1 To Application.WorksheetFunction.CountA(Sheet11.Range("A:A"))
a = Len(TextBox1.Text)
If UCase(Left(Sheet11.Cells(i, 2).Text, a)) = UCase(Left(TextBox1.Text, a)) Then
ListBox1.AddItem Sheet11.Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet11.Cells(i, 2).Value
End If
If UCase(Left(Sheet11.Cells(i, 3).Text, a)) = UCase(Left(TextBox1.Text, a)) Then
ListBox1.AddItem Sheet11.Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet11.Cells(i, 2).Value
End If
If UCase(Left(Sheet11.Cells(i, 4).Text, a)) = UCase(Left(TextBox1.Text, a)) Then
ListBox1.AddItem Sheet11.Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet11.Cells(i, 2).Value
End If
If UCase(Left(Sheet11.Cells(i, 9).Text, a)) = UCase(Left(TextBox1.Text, a)) Then
ListBox1.AddItem Sheet11.Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet11.Cells(i, 2).Value
End If
Next i
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much
ASKER
Update on this to help the next person.
Code below.
Code below.
- Private Sub TextBox1_Change()
- Dim I As Long
- Dim strSearch As String
- TextBox1.Text = StrConv(TextBox1.Text, vbProperCase)
- TextBox1.SetFocus
- ListBox1.Clear
- strSearch = "*" & UCase(TextBox1.Text) & "*"
- 'Note: Why For I = 10 as I have no info in rows 1 to 9 this is a heading. So change 10 to 1 if don't what to skip rows.
- For I = 10 To Application.WorksheetFunct
ion.CountA (Sheet11.R ange("A:A" )) - 'This code will search columns 2,3,4 & 9.
- If UCase(Sheet11.Cells(I, 2).Text) & UCase(Sheet11.Cells(I, 3).Text) & UCase(Sheet11.Cells(I, 4).Text) & UCase(Sheet11.Cells(I, 9).Text) Like strSearch Then
- With ListBox1
- .AddItem Sheet11.Cells(I, 1).Value
- .List(.ListCount - 1, 1) = Sheet11.Cells(I, 2).Value
- End With
- End If
- Next I
- End Sub
ASKER
I have been working on this for a week watching videos and trying all kinds of things.