CODEWITHSUNDEEP
excelvba
Vaupell
Vaupell

Reputation: 105

how can i use get the columns data when looking through them?

I have multiple sheets, each with 1 only 1 table at various widths and heights.

I am trying to achive: once user have selected sheet via combobox, (this works) then i can list the headers from the table on that sheet.

my possible solution idea: My idea was to list the table headers in a combobox on a userform.

  1. i count columns on selected sheet, works
  2. for loop through the columns to grab the header name from each and stack into combobox.list,

code:

Private Sub chcSite_Change()
Dim siteSheet As String
siteSheet = WorksheetFunction.VLookup(Me.chcSite.Value, Worksheets("Overview").Range("SiteTable"), 2, False)

Me.chcRange.Enabled = True  ' enables combobox for headers list

Dim COLS As Integer
COLS = Worksheets(siteSheet).ListObjects(1).ListColumns.Count
Dim i As Integer
i = 1

For i = 1 To COLS   
    If Worksheets(siteSheet).Cells(Columns(i), 1) = "" Then Exit For   ' if header is empty = also end of table cols.
    MsgBox Worksheets(siteSheet).Cells(Columns(i), 1)  ' debug to see what it returns.

Next i


'Me.chcRange.List = Worksheets(siteSheet).ListObjects(1).ColumnHeads ' random test of columnheads

End Sub

as you can see i was exspecting Worksheets(siteSheet).Cells(Columns(i), 1) to return something, but it appears it is only a pointer/selector.

Upvotes: 1

Views: 38

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

You might benefit from reading The VBA Guide To ListObject Excel Tables.

For example to get the 3rd heading of a table use 

.ListObjects("Table1").HeaderRowRange(3)

The ListObject has its own row/column numbering and may be different from the sheets row/column numbering.

It should look something like this:

Dim i As Long 'always use Long
'i = 1 not needed

For i = 1 To COLS   
    ' v-- this part is not needed …
    If Worksheets(siteSheet).ListObjects(1).HeaderRowRange(i) = "" Then 
        Exit For   ' if header is empty = also end of table cols.
    End If
    ' ^-- … because headers of ListObjects can not be empty by definition.
    '       And HeaderRowRange can only access the headers of the ListObjects.

    MsgBox Worksheets(siteSheet).ListObjects(1).HeaderRowRange(i)  ' debug to see what it returns.
Next i

So you can shorten it to: 

Dim i As Long 
For i = 1 To COLS   
    MsgBox Worksheets(siteSheet).ListObjects(1).HeaderRowRange(i)  ' debug to see what it returns.
Next i

Note that Cells(Columns(i), 1) could not work because eg Columns(2) references the complete column B (it is a range representing the full column 2) and Cells needs a row/column number like Cells(row, column).

Upvotes: 1

Related Questions