Search Results to Listbox

[gwoodby]

Please Help if you can
Column A has last name, first name
Say these names are in Column A
Column A Row
Hoover, John 6
Hoover, Rick 10
Hoover, Jordan 20

Does the Search, all 3 should come up in the list box/ComboBox/
Dropdown
Wichever is easiest,
When i select Rick, It should select Rown 10 Column A Then get the
Data From Row B10
D10, and F10

 

[JLGWhiz]

It would make things a lot easier if you put the last name in one column and
the first name in another. Although, it can be done with it as it is, the
code is a lot less complicated if the two elements are separated.

 

[JLGWhiz]

After reading this again, it really is not necessart to separate them. I had
read it that you wanted to search for the name with "Rick", but you want to
select from a list box or combo box.

1. Use column A to load the list or combo box.
2. In the click event of the control, use the Find method to search Range
A2 through the last row with data.
3. When found, use the offset method to capture the cells B, D and F on the
same row into a variable.
4. Use the variable to do whatever you need to do with the data.

 

[Joe]

After reading this again, it really is not necessart to separate them. I had
read it that you wanted to search for the name with "Rick", but you want to
select from a list box or combo box.

1. Use column A to load the list or combo box.
2. In the click event of the control, use the Find method to search Range
A2 through the last row with data.
3. When found, use the offset method to capture the cells B, D and F on the
same row into a variable.
4. Use the variable to do whatever you need to do with the data.






- Show quoted text -

Hi

I will do it the foloowing way.

1) Define a range in module like this

Public rng_Levels As Range


2) Initialise it in "This Workbook" the following way.. edit the
worksheet to urs.

Private Sub Workbook_Open()

With ThisWorkbook.Worksheets("Levels")
Set rng_Levels = .Range("A2:B20")
End With

End Sub

3) Add the items to a listbox (named Listbox1)

n_Col = 1
n_Row = 2

Do While rng_Levels(n_Row, n_Col).Value <> ""
Listbox1.additem rng_Levels(n_Row, n_Col).Value
n_Row = n_Row + 1
Loop


once it is selected, write to a specific cell and use VLookup to
retieve its correcponding data.

Try it..
HTH
Joe