PDA

View Full Version : Solved: Search all the matching names



JohnyG
09-16-2008, 02:49 AM
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
09-16-2008, 04:14 AM
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
09-16-2008, 04:54 AM
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
09-16-2008, 05:03 AM
Its already working that ways...

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

Krishna Kumar
09-16-2008, 05:03 AM
Did you try the code? It comes with only unique names.

JohnyG
09-16-2008, 05:04 AM
Its already working that ways...

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