Addressing column header name in FOR EACH loop

[dotsent]

Hi, this seems a simple question, but just can't crack it. Have used plenty of FOR EACH loop within ListObjects in similar ways as the code below. However now come across a real legacy system with obscene amount of columns, making me want to address columns over header names instead of column sequence numbers while running IF-clause. E.g. instead of lstrw.Range(20), I'd like to address it by header name, like lstrw.Range(["Date"]) and not have to keep counting and mapping the columns through numbers. I guess the code would also be more bulletproof against column sequence changes and have better readability.

Have tried different iterations and no suitable leads googling.

lstrw.Range(["Date"])

gives error message:

Invalid procedure call or argument

Dim MyTable As ListObject
Dim lstrw As ListRow

Set MyTable = ActiveSheet.ListObjects("Data")
For Each lstrw In MyTable.ListRows
    If lstrw.Range(1) = "A" Then
            lstrw.Range(2) = "B"
    End If
Next lstrw

 

[dmt32]

Hi,

give following a try & see if helps

Dim MyTable As ListObject
Set MyTable = ActiveSheet.ListObjects("Data")

MsgBox ActiveSheet.Range(MyTable.Name & "[Date]").Column

'or another way maybe

MsgBox MyTable.ListColumns("Date").Index

hopefully, either will return the column number

Dave

 

[dotsent]

Thank you, both worked.

 

[dmt32]

Thank you, both worked.

welcome - appreciate feedback

Dave