Populate Listbox from Array

[ccordner]

I'm not very experienced at working with arrays in vba and this is probably very simple.

I want to populate a listbox with an array of unknown size (three columns, not sure how many rows) from the sheet "SAP Codes" starting from row 2.

The code I have below doesn't seem to work.


Dim arrSAPCodes() As String

Private Sub UserForm_Initialize()

'Populate SAP Codes
arrSAPCodes = .Range(.Range("A2"), .Range("C" & Rows.Count).End(xlUp))
lstSAPCodes = SAPCodes

End Sub

I'm sure it's something obvious

Incidentally, when I have done this, how do I refer to an individual item of the array? For example, if the first column contains unique code and I have a unique code, how do I find the corresponding entry in the second column?

 

[tweedle]

You don't really need to handle an array to load a multi-column listbox:
Here's a non-Array version:

Private Sub UserForm_Initialize()
    Sheets(1).Activate
    lastrow = Range("C" & Rows.Count).End(xlUp).Row
    With ListBox1
    .ColumnCount = 3
    .ColumnWidths = "35;35;35" 'points
    .BoundColumn = 1    'if it's bound to a cell, column 1  is returned
    .ControlSource = "Sheet1!E2"
    .RowSource = "A2:C" & lastrow 'The array of cells that provide values
    End With
End Sub

And to get the values back out of the click event:

Private Sub ListBox1_Click()
    With ListBox1
        MsgBox .ListIndex & vbCrLf & _
        .List(.ListIndex, 0) & vbCrLf & _
        .List(.ListIndex, 1) & vbCrLf & _
        .List(.ListIndex, 2)
    End With
End Sub