display data based on a combobox

  • Thread starter Thread starter dansalmada
  • Start date Start date
D

dansalmada

Hi

I have a table full with data (name, date, quantity, amount, etc...) on
sheet2

In sheet1, I have a combobox with all of the names on the table and I
need to know how to display automatically the data from sheet2 for only
the name I select from the combobox in sheet1.

I was thinking sending the value of the combobox to a cell and making a
vlookup for that value on sheet2, but I only get the first value.

Please help

Thanks

Daniel
 
Daniel,
One option is to use MATCH to find the name (and hence row) in
sheet2 and then read the data into an array

Assume data is in columns A to H with A containing name.

Dim Data as variant
Dim ws2 as worksheet

set ws2=worksheets("sheet2")

Myname=Worksheets("sheet1").range("a5") ' <=== your Combobox selection

row = Application.Match(MyName, ws2.Range("A:A"), 0)

If Not IsError(row) Then ' <==== match found

Data = ws2.Range("B" & row & ":H" & row) ' Read data into array

For r = LBound(Data, 1) To UBound(Data, 1)
For c = LBound(Data, 2) To UBound(Data, 2)
Debug.Print Data(r, c)
' ... Assign to cells in Sheet1
Next c
Next r
End If


HTH
 
Back
Top