Send all items from the listbox to worksheet table-CodePudding

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"

worksheet "RIS" and table named "Items"

To become like this

CodePudding user response：

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