Having issue with HeaderRowRange to loop through all headers and change their values

Question

I was trying to loop through all headers in my table on Excel to change their values, unless there is a better approach.

The idea is to check if the table already exists and if it does not, create a new table, however and since it's not possible (to my knowledge) to create the table and give names to headers before creating, I have to change their names after. For that I was trying to use the .HeaderRowRange.Address to loop true all cells in that range and change the values of headers, but the With tCells is not being triggered

This is the code I have:

Sub novaLinha()
Dim iRow As Long
Dim ws As Worksheet
Dim tbl As ListObject
Dim tCells As Ranges
Dim tCell As Range

On Error Resume Next

Set ws = Worksheets("Dados")

If ws.ListObjects.count Then

Else
    Set tbl = ws.ListObjects.Add(xlSrcRange, Range(cells(1, 1), cells(1, 5)), , xlYes)

    With tbl
        .Name = "TabelaDados"
        .TableStyle = "TableStyleMedium2"
        Set tCells = Range(.HeaderRowRange.Address)
        Debug.Print Range(.HeaderRowRange.Address)
        With tCells
            Debug.Print tCells
            For Each tCell In tCells
                Debug.Print tCell
            Next tCell
        End With
    End With
End If

'iRow = ws.Cells(Rows.count, 1).End(xlUp).Row
'ws.Cells(iRow, 1).Value = ws.ListObjects.count
Exit Sub
    If Err.Number > 0 Then
        Debug.Print Err.Number & ":" & Err.Description
    End If
End Sub

Thanks in advance

Answer 1

Try this to iterate over the tbl.HeaderRowRange.Cells directly:

With tbl
    .Name = "TabelaDados"
    .TableStyle = "TableStyleMedium2"
    For Each tCell In tbl.HeaderRowRange.Cells
        Debug.Print tCell
    Next tCell
End With

Note comments above re: error-handling:

• You are using Exit Sub before your error handler. So even if Err.Number > 0, you won't print the error.
• On Error Resume Next simply tells the IDE to ignore any and all errors. This means that errors can compound and lead to further errors, which then become the more difficult to troubleshoot. It's generally frowned upon to use On Error Resume Next especially when you've wrapped the entire executable body of your subroutine in that sort of handler. There are some limited/single-use functions that make use of this construct, but that is not a good idea to implement in this case.

it's not possible (to my knowledge) to create the table and give names to headers before creating

Here this is possible, if you first populate the cells that will comprise the range, and then add a table:

Dim hdrRange As Range
Set hdrRange = Range("A1:E1")
Const HEADERS As String = "Alpha,Beta,Gamma,Delta,Epsilon"
hdrRange.Value = Application.Transpose(Application.Transpose(Split(HEADERS, ",")))
' Creates table with the headers already in place:
Set tbl = ws.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(1, 5)), , xlYes)

With tbl
    .Name = "TabelaDados"
    .TableStyle = "TableStyleMedium2"
    For Each tCell In tbl.HeaderRowRange.Cells
        Debug.Print tCell
    Next tCell
End With