VBA - loop through a table and update another column using column name

Question

In Excel VBA, I am trying to loop through an Excel Table and then update another column in that row. I wish to refer to the other column by its name rather than an offset/number in case the layout changes. Is that possible?

Dim rw As ListRow
Dim cl As Range

For Each rw In ActiveSheet.ListObjects("MasterData").ListRows
    'Pseudo code:
     if (rw[Notes] = "Test") then DO SOMETHING 'In this line I'd like to refer to the column called "Notes" but not sure how to do it.
Next rw

Vityata Vityata · Accepted Answer · 2017-08-31T11:31:49

The Intersect function will do about 90% of the job. The rest is like this:

Option Explicit

Sub TestMe()

    Dim rw          As Range
    Dim cl          As Range
    Dim rngCell     As Range

    For Each rw In [MyTable].Rows
        Set rngCell = Intersect(rw, [MyColumnName])
        If Not rngCell Is Nothing Then
            If rngCell = "Text" Then Debug.Print rngCell.Address
        End If
    Next rw

End Sub

[MyTable] is the name of the table.
[MyColumnName] is the name of the column.

VBA - loop through a table and update another column using column name

1 Answers