Retrieve ListObject column from cell as a string-CodePudding

Assume I have a ListObject and I'm iterating over its rows with a for each loop. What I want to do is to further iterate over every cell inside the row and retrieve the ListObject column of this cell as a string.

I know you can get the cell column with cell.column but is there a way to retrieve the ListObject column as well?

EDIT: I expect the function to return the text from the header row of the ListObject.

CodePudding user response：

Please test the next code. It is strange to return the headers so many times your code iterates by rows, so I imagined a piece of code returning the value for each row/column and the corresponding column header:

Sub testTableColumnByRow_Column()
  Dim sh As Worksheet, tbl As ListObject, rngDBR As Range, iRow As Long, iCol As Long
  Set sh = ActiveSheet 'use here the sheet you need
  Set tbl = sh.ListObjects("your Table") 'use here the real table name
  Set rngDBR = tbl.DataBodyRange
  For iRow = 1 To rngDBR.rows.count
     For iCol = 1 To tbl.Range.Columns.count
        'it returns the iteration (row, column) in dataBodyRange and corresponding column header:
        Debug.Print rngDBR(iRow, iCol).value, tbl.HeaderRowRange.cells(1, iCol).value
     Next iCol
  Next iRow
End Sub

If you need to determine the table column of a selected cell, inside the table, please try the next way:

Sub testSelectedCellInTable()
   Dim aC As Range
   Set aC = ActiveCell
   If Not aC.ListObject Is Nothing Then 'if it is inside a table
        Debug.Print aC.ListObject.HeaderRowRange.Columns(aC.column - aC.ListObject.Range.column   1).value
   End If
End Sub

CodePudding user response：

Return Header (String) of an Excel Table Cell

You could use the StrTableCellHeader function. It is simplified ('dirty') under the assumption that you're not interested in why it may have failed i.e. returned an empty string.

Option Explicit

Function StrTableCellHeader(ByVal Cell As Range) As String
    On Error Resume Next ' defer error trapping
    StrTableCellHeader = CStr(Cell.EntireColumn _
        .Rows(Cell.ListObject.HeaderRowRange.Row).Value)
    On Error GoTo 0 ' disable error trapping
End Function

Sub StrTableCellHeaderTEST()
    Dim Cell As Range: Set Cell = Range("B5")
    Debug.Print StrTableCellHeader(Cell)
    ' or just e.g.:
    Debug.Print StrTableCellHeader(Range("G3"))
    ' Results:
    ' Col2
    ' Col7
End Sub

Sub StrTableCellHeaderTESTErrors()
    ' The following 'Debug.Print' lines return an empty string,
    ' the default value of 'StrTableCellHeader' since an error occurred
    ' in the function.
    
    Dim Cell As Range
    
    ' The cell is 'Nothing' (e.g. forgotten to create a reference)
    ' ('.EntireRow' failed).
    Debug.Print StrTableCellHeader(Cell)
    ' Run-time error '91': Object variable or With block variable not set
    
    ' The cell is not in an Excel table ('.ListObject' failed).
    Set Cell = Range("L10")
    Debug.Print StrTableCellHeader(Cell)
    ' Run-time error '91': Object variable or With block variable not set
    
    ' 'Cell' is not a single cell
    ' ('CStr' failed because '.Value' is an array, not a simple data type).
    Set Cell = Range("A1:B2")
    Debug.Print StrTableCellHeader(Cell)
    ' Run-time error '13': Type mismatch

End Sub