Solved: Search all the matching names

[JohnyG]

Hi,

I am trying to write a code to search a specific name in Column. I already have a code that search for the existence of a name and give the output.

However, now I am looking for an additional functionality for instance?

If I type ?AND? in textbox and click on search button then it will check Column A and display all names (like Andy, Andrew, Anders, Anderson, Andre) starting with ?AND? in the list box. Later when I select any name in list box it will get updated in same Textbox.


Please help?.

[Krishna Kumar]

Hi,

Replace your code with the following.

Option Compare Text
Private Sub CommandButton1_Click()
Dim FindString As String
Dim Rng As Range, a, v, z, n As Long
FindString = TextBox1.Value
If Trim(FindString) <> "" Then
a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each v In a
If Not IsEmpty(v) Then
If InStr(1, v, FindString, vbTextCompare) Then
If Not .exists(v) Then .Add v, Nothing
End If
End If
Next
z = .keys
End With
Erase a
If UBound(z) > 0 Then
With Me.ListBox1
.Clear
.List = Application.Transpose(z)
End With
Else
MsgBox "Name not Found in database"
End If
End If
End Sub

HTH

[JohnyG]

Hi Krishna,

Thank you so much for your help.

In case if we have repetitive list of names then how can I get only unique values. For instance Let say Andrew Todd keep on repeating for 2-3 times and I only want to select it once in the list box.

Andrew ToddAnders JayAndre ColemanAndrews NapoloeanDavid IveyJames Crawford LloydKeith W HillerAndrew ToddAndy PareraMichael NowellTodd BowmanAnderson CoffeyWilliam GillAndrew Todd

[JohnyG]

Its already working that ways...

Thank you so much for your help .... :clap:

[Krishna Kumar]

Did you try the code? It comes with only unique names.

[JohnyG]

Its already working that ways...

Thank you so much for your help .... :clap: