TonySondergeld
New Member
- Joined
- Jul 11, 2009
- Messages
- 31
Hi all require some help with this one.
I am try to fill a list box on a userform that would have 13 columns per added items (yes I know you can only set it to 10) from data obtained from a 4 criteria match to a worksheet range. When all 4 criteria are matched in a per row searched it obtains the 13 columns of data and add the item to the list box. I have try putting the matched data into a array but I can at time get in to insert the found data but only in the first column and the other data will go in the rows under it not the columns.
Thank for any help given.
the code i an try to get to work this so far I could be way off possible.
I am try to fill a list box on a userform that would have 13 columns per added items (yes I know you can only set it to 10) from data obtained from a 4 criteria match to a worksheet range. When all 4 criteria are matched in a per row searched it obtains the 13 columns of data and add the item to the list box. I have try putting the matched data into a array but I can at time get in to insert the found data but only in the first column and the other data will go in the rows under it not the columns.
Thank for any help given.
the code i an try to get to work this so far I could be way off possible.
Code:
Private Sub Cmb_Lookup_Click()
Dim pArray() As String
Dim val(13) As Variant
' -- Set the worksheet name --
Set ws = Worksheets("Product Search")
' -- Clear the listbox of previous data --
Me.Lbx_Stocklist.Clear
' -- For loop --
For i = 14 To 66000
' -- Listbox add data to --
With Me.Lbx_Stocklist
' -- 4 criteria search --
If ws.Range("F" & i).Value = Me.Cbx_CoA_Product.Value And ws.Range("K" & i).Value = Me.Cbx_CoA_Customer.Value And ws.Range("L" & i).Value = Me.Cbx_CoA_Status And ws.Range("M" & i).Value = Me.Cbx_CoA_Availiabity.Value Then
' -- Matched Data Found --
val1 = ws.Range("F" & i).Value ' Product
val2 = ws.Range("G" & i).Value ' Batch
val3 = ws.Range("H" & i).Value ' Pallet
val4 = ws.Range("I" & i).Value ' Bags
val5 = ws.Range("J" & i).Value ' Best Before
val6 = ws.Range("N" & i).Value ' Orientation
val7 = ws.Range("O" & i).Value ' Protein
val8 = ws.Range("P" & i).Value ' Moisture
val9 = ws.Range("Q" & i).Value ' Sieve 1
val10 = ws.Range("R" & i).Value ' Sieve 2
val11 = ws.Range("S" & i).Value ' Sieve 3
val12 = ws.Range("T" & i).Value ' Sieve 4
val13 = ws.Range("U" & i).Value ' Thru's
' -- matched data into the array --
[B]' --- tried variations on this array ---[/B]
pArray = CreateTextArrayFromSourceTexts("val1", "val2", "val3", "val4", "val5", "val6", "val7", "val8", "val9", "val10", "val11", "val12", "val13")
' -- Array data into a listbox --
[B]' --- also tried variations on how to insert the array in to the listbox ---[/B]
Lbx_Stocklist.AddItem pArray
End If
End With
Next i
End Sub