iterate through cells in new row of table using column names

vwankerl · May 17th 2017

I have a Table that I need to add rows to and then place data in a couple of the cells in the new row. I am able to create the new row but do not the syntax for referencing the cells by their column name rather than an index. Basically I want to use structured referencing in VBA like I can in formulas.

**KjBox** · May 18th 2017

Re: iterate through cells in new row of table using column names

Maybe something like this

Code

Sub GetColumnByName()
    Dim Headers, ColHeads, i As Long
    
    ColHeads = Array("Data1", "Data2", "Data3") 'The column headers that you need to reference
    
    With Sheets("Sheet3").ListObjects("Table1") 'Change sheet and table names to suit
        Headers = .HeaderRowRange
        .ListRows.Add
        With .ListRows(.ListRows.Count).Range
            .Columns(Application.Match(ColHeads(0), Headers, 0)) = "ABC"
            .Columns(Application.Match(ColHeads(1), Headers, 0)) = 123
            .Columns(Application.Match(ColHeads(2), Headers, 0)) = "XYZ"
        End With
    End With
    
End Sub

Display More

iterate through cells in new row of table using column names

Participate now!

Share