Addressing column header name in FOR EACH loop

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
lstrw.Range(["Date"])
gives error message:

Invalid procedure call or argument

VBA Code:
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,595
Office Version
  1. 2019
Platform
  1. Windows
Hi,

give following a try & see if helps

VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,132,914
Messages
5,655,930
Members
418,253
Latest member
TheJackal26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top