Populating a dropdown list with an Array

[MC]

I am using data from a separate worksheet as a look-up for
the data entry on a primary sheet. After a part no. is
entered I'd like the dropdown list for the possible serial
numbers to be populated by an array. Of course the serial
numbers are different and a specific part no. could have 1
to N serial numbers associated with it.

1. Doing the search is not a problem
2. Determining the indeterminate range of values and
loading it in the array may give me some difficultly -
suggestions or where to look would be appreciated.

3. Loading a dropdown list is definitely where I may
lack the adequate syntax and knowledge.


I found an example of populating a User's Form listbox on
the support page, but I am not certain it is a possibility.

Thanks for any help.

MC

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

The answer would depend upon how the data is formatted and what DD you are
using (a userform, a worksheet forms DD, Data Validation, or worksheet
control).

Give us the details.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

for each cell in Worksheets("Data").Range("A1:A5000")
if cell.Value = Worksheets("sheet1").Range("C1").Value then
Worksheets("sheet1").Listbox1.AddItem cell.offset(0,1).value
end if
Next

would be one possibility. if you want to build an array

Dim Myarray()
num = Application.Countif(rksheets("Data").Range("A1:A5000"), _
Worksheets("sheet1").Range("C1").Value)

Redim MyArray(1 to num)
j = 0
for each cell in Worksheets("Data").Range("A1:A5000")
if cell.Value = Worksheets("Sheet1").Range("C1").Value then
j = j + 1
myarray(j) = cell.offset(0,1).value
end if
Next
Worksheets("Sheet1").Listbox1.List = myArray