vba array into combobox userform

[paynod]

I have created the following procedure which reurns an array of products from a function. I want to put the results into a combobox on a userform. How do I prepare the array so it can be passed into a combobox?


Sub getProducts()
Dim list As ListObject
Dim INDEX_ARRAY As Range, INDEX_COLUMN As Range, INDEX_ROW As Range, listColumnArray As Range, LACode As Range
Dim Result As Variant, y As Variant, myArray As Variant
Dim x As Long, i As Long, j As Long
Set LACode = Worksheets("Sheet2").Range("$E$4")
Set y = Worksheets("Label_Gen").Range("$u$5")
With Worksheets("Label_locs")
Set list = .ListObjects("Table3")
Set listColumnArray = list.ListColumns(11).Range
Set INDEX_ARRAY = list.ListColumns(1).Range
For x = 0 To y - 1
Result = Application.Index(INDEX_ARRAY, Application.Match(LACode, listColumnArray, 0) + x)
myArray = Array(Result)
For j = LBound(myArray) To UBound(myArray)
ReDim Preserve myArray(j)
'Debug.Print myArray(j)
Next j
Next x

End With
End Sub

[p45cal]

I have created the following procedure which reurns an array of products from a function.
I don't see how it does produce an array! It'll find the first match then it seems to go through the next n values below that (n being whatever's in U5).
But the array never has more than one value in it!

So what are you trying to get? A list of items in column 1 of the table3 where there's a match in column 11 of table3 with whatever's in E5? And you want to limit the number of results to whatever's in U5?

[p45cal]

Oh yuk, you've cross posted: http://stackoverflow.com/questions/31515968/vba-index-result-dropdown-menu
Anywhere else?
paynod, please have a read of http://www.excelguru.ca/content.php?184
Ultimately it will be to your benefit.

[paynod]

Thanks p45cal for your reply.
Yes, exactly. I want to return each result from column1 as a list in a combobox on a userform limiting those results by the value in U5. How do I loop through each 'Result' and then add the results to a combobox drop down menu?
The userform is named userform1 and the combobox is named productCode if that helps?....

Regards,
Tim

[paynod]

No. I only cross posted because I had no reply on stack overflow. Someone else recommended this forum so I joined.

[paynod]

Hi p45cal,
I have deleted the post on Stackoverflow. To be honest I din't know you could easily delete them. Anyway, now this is the only place this has been posted. Please accept my apologies.
Kind regards, Tim

[p45cal]

Try this (not the best):
Sub getProducts2()
Dim list As ListObject
Dim listColumnArray As Range, cc As Range, LACode
Dim y As Long, myArray(), x As Long

LACode = Worksheets("Sheet2").Range("$E$4").Value
y = Worksheets("Label_Gen").Range("$u$5").Value
If y > 0 Then
ReDim myArray(1 To y)
With Worksheets("Label_locs").ListObjects("Table3").ListColumns(11).Range
Set cc = .Find(what:=LACode, LookIn:=xlFormulas, Lookat:=xlWhole, searchformat:=False) 'te4st this for finding first cell
If Not cc Is Nothing Then
firstaddress = cc.Address
x = 1
Do
myArray(x) = cc.Offset(, -10).Value
x = x + 1
Set cc = .Find(what:=LACode, after:=cc, LookIn:=xlFormulas, Lookat:=xlWhole, searchformat:=False)
Loop Until x > y Or firstaddress = cc.Address
End If
End With
If Not cc Is Nothing Then
ReDim Preserve myArray(1 To x - 1)
productCode.list = myArray
'UserForm1.productCode.list = myArray 'instead of the above line depending on where you have the code.
Else
MsgBox "nothing found"
End If
Else
MsgBox "U5 contains zero"
End If
End Sub

[paynod]

Hi p45cal,
Thank you for sending me your solution. I get the message "nothing found". There is a formula in table 3 column 11 which extracts a number from a label code. Could the fact it's a formula be causing the problem?....

Regards, Tim

[p45cal]

Yes.
Change both instances of
Lookin:= xlFormulas
to
Lookin:= xlValues

but make sure there are no hidden rows in the sheet you are searching as it now won't search among hidden rows.

[paynod]

pa45cal,
Thanks very much for your help. It works perfectly. Incidentally, what is the advantage of using find over index?
Regards,
Tim

[p45cal]

Incidentally, what is the advantage of using find over index?Index by itself won't do it at all, I'm guessing you mean Index with Match. Match (using an exact match) will find the first instance only in a range, so you have to keep on adjusting where Match looks for a match - not that difficult, but .find has an After parameter to tell it where within a range to start looking (incidentally, .find has a .findnext but I don't use it).