Fill a multicoulmn Listbox with with matching data from 13 coulmns worksheet range using 4 Criteria matchs


New Member
Jul 11, 2009
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.

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 --
'  -- 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

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...