Populate Excel 2010 listbox with column headings

I have a database in a worksheet (called “Database”) that runs from cell A1 to ET4160 at the moment (it always expands downward and occasionally rightward). I want to populate a listbox with the database’s column headers, which run from G1 to ET1.  I thought the best way to do this was to collect the header items into an array and populate the listbox from the array, but it’s not working. I get a runtime 13; type mismatch on the ‘For i = 1 To UBound(myVar)‘ statement. Here is the relevant part of the code:

Dim Wks As Excel.Worksheet
Dim myVar As Variant, I as integer

Set Wks = ThisWorkbook.Worksheets("Database")
With Food.LstItem

If WorksheetFunction.CountA(Cells) > 0 Then
'Determine last used column of Database (by searching backwards by columns)
LastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
End If

'Convert column number to alpha:
If LastCol > 26 Then
    lastColAlpha = Chr(Int((LastCol - 1) / 26) + 64) & Chr(((LastCol - 1) Mod 26) + 65)
    lastColAlpha = Chr(LastCol + 64)
End If

myVar = Wks.Range("G1:I1").Value
myVar = Application.WorksheetFunction.Transpose(ListItems) 'convert values to vertical array

For i = 1 To UBound(myVar)
      .AddItem myVar(i) 'populate the listbox
      Next i
  .ListIndex = -1 ' means no items selected, set to 0 to select the first item
End With

Open in new window

I know arrays are powerful but my fluency with them is weak - can someone tell me what I'm missing here?
Who is Participating?
Robert SchuttSoftware EngineerCommented:
After looking at myVar in the watch window I used this code to put all headings in the listbox:
        myVar = Wks.Range("A1:" & lastColAlpha & "1").Value
        For I = 1 To UBound(myVar, 2)
            .AddItem myVar(1, I) 'populate the listbox
        Next I

Open in new window

marrick13Author Commented:
That does the trick - thank you very much.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.