Send all items from the listbox to worksheet table

Gerald Lariosa 0
Viewed 30

How can I send all items from the listbox to worksheet table? Tried this code but it's not working.

Worksheets("RIS").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = lbCart.List(i)

ListBox named "lbCart"

ListBox named "lbCart"

worksheet "RIS" and table named "Items"

worksheet "RIS" and table named "Items"

To become like this

To become like this

vba excel
1 Answers

I notice that your lbCart columns don't align with those on your spreadsheet, so you need a way to write the data to the specific sheet columns of your template. This makes the task slightly more complicated than the usual copy list to variant, copy variant to range process:

Dim x
x = Me.lbCart.List
Range("C2").Resize(UBound(x) + 1, UBound(x, 1)).Value = x

I came up with this - which will allow you to define where each column goes by cycling through each cell:

columnarray = Array("C", "B", "D") ' first column writes to C, then B, then D

With Worksheets("RIS")
    For c = 0 To UBound(columnarray)
        For r = 0 To lbCart.ListCount - 1
            Range(columnarray(c) & "2").Offset(r).Value = lbCart.List(r, c)
        Next
    Next
End With
CLR0
Related