[SOLVED] For Each Loop with Column Names

For Each Loop with Column Names

I am looking for the correct syntax and/or example to use column names instead of row/column references within a For Each structure (to iterate thru an excel data table within a worksheet). I need to be able to read and update cells within the data table. I have tried many different variations, along with much googling, but no joy.

Here is my example code snippet, using row/column references:

For Each rowData In ActiveSheet.Range("DataTable").Rows
    SavedValue = rowData.Cells(1, 2)
    rowData.Cells(1, 3) = "New Value"
Next

Thanks in advance for any and all assistance.

Solution

Try something like this:

Dim rw As Range, lo As ListObject

Set lo = ActiveSheet.ListObjects("DataTable")

For Each rw In lo.DataBodyRange.Rows
    SavedValue = rw.Cells(lo.ListColumns("ThisColumn").Index).Value
    rw.Cells(lo.ListColumns("ThatColumn").Index).Value = "New Value"
Next

Edit - an example of wrapping the ListObject in a class for a nicer access syntax:

clsListObject

Option Explicit

Private lo As ListObject
Private dict As Object
Dim c As Range

Property Set ListObject(lstobj As ListObject)
    Set lo = lstobj
    Set dict = CreateObject("scripting.dictionary")
    dict.CompareMode = 1 'case-insensitive
    For Each c In lo.HeaderRowRange 'map column indexes
        dict(c.Value) = lo.ListColumns(c.Value).Index
    Next c
End Property

'access the underlying listobject
Property Get ListObject() As ListObject
    Set ListObject = lo
End Property

'access a single cell via row index and column header
Property Get Cell(r As Long, hdr As String) As Range
    If dict.Exists(hdr) Then
        Set Cell = lo.DataBodyRange.Cells(r, dict(hdr))
    Else
        'handle missing header
    End If
End Property

Property Get ListCount() As Long
    ListCount = lo.ListRows.count
End Property

Example usage:

Sub Tester()

    Dim lo As New clsListObject, r As Long
    
    Set lo.ListObject = ThisWorkbook.Sheets("Abbreviations").ListObjects("Abbrevs")
    
    For r = 1 To lo.ListCount
        Debug.Print lo.Cell(r, "OrigWord"), lo.Cell(r, "Abbrev") 'read cells
        lo.Cell(r, "Abbrev") = lo.Cell(r, "Abbrev") & "_updt"    'update cells
    Next r
    
End Sub

Whether it's worth the effort or not depends on how much code you're writing to work with listobjects and how useful it is to factor out common patterns into a separate class.