"Find All" Code Results Information Storage

[glynnmack]

I'm a bit of a visual basic noob, and may be in over my head here...

I'm having difficulty using the following "repeating search code" to store the row information of the results, allowing me to display them on a user form label box later.


Private Sub getVendorButton_Click()

'Search Prompt
ProductSearch = InputBox ("Search Vendor List")

'Declare foundCell as the Search Result
Set FoundCell= _ Sheets("List").Range("A1:AZ10000").Find(what:=ProductSearch, _ after:=LastCell)

'Store information from other columns in FoundCell's row
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Row
firstEmail = Sheets("List").Cells(FirstAddr, 4).Value
firstLastName = Sheets("List").Cells(FirstAddr, 1).Value
firstFirstName = Sheets("List").Cells(FirstAddr, 2).Value

'Display Results in a Listbox
LastNameListBox.AddItem firstLastName & ", " & firstFirstName
End If

'Repeat until the entire sheet has been searched, resulting in a duplicate
Do Until FoundCell Is Nothing
Debug.Print FoundCell.Row
Set FoundCell = _ Sheets("List").Range("A1:AZ10000").FindNext(after:=FoundCell)

If FoundCell.Row = FirstAddr Then
Exit Do
End If

' Store information from other columns in second, third, etc FoundCell's row
If Not FoundCell Is Nothing Then
secondAddr = FoundCell.Row
secondEmail = Sheets("List").Cells(secondAddr, 4).Value
secondLastName = Sheets("List").Cells(secondAddr, 1).Value
secondFirstName = Sheets("List").Cells(secondAddr, 2).Value

'Display results in a Listbox
LastNameListBox.AddItem secondLastName & ", " & secondFirstName
End If
Loop

End Sub


The next step is to get the user form to display the corresponding email address when I select the name from the listbox. But all I can come up with is the following code:


Private Sub LastNameListBox_Click()

SelectedName = LastNameListBox.Value
SelectedEmail = Sheets("List").Cells(secondAddr, 4).Value
Label3.Caption = SelectedEmail

End Sub


Basically, I'm either able to show email's from the FirstAddr or the secondAddr (which is showing the email from the last result of the search, presumably because that is the secondAddr before the search ends because of duplicates).

Can anyone think of a technique to get me through this? I've been staring at my screen for a day or so now...

Thanks!