Reference Column by Header Name

[Jarubell]

I have a table "Index" in a worksheet and I would like to reference the column by the header name, "Client Tag", not "l". Tryin to avoid having to change my code when a job requires me to insert a column.

Currently, my code looks like;

Please Login or Register  to view this content.

[Croweater]

Use a named range for your column, such as "ClientTag"

Then you can use Range("ClientTag")(aRow,1).Value ....

(To create a named range highlight your column and type your name in the top left box...)

[karmapala]

Maybe :

Please Login or Register  to view this content.

I found the code in this link

[LJMetzger]

Hi Jarubell,

@Croweater's suggestion to use 'Named Ranges' works well in theory but, 'Named Ranges' in my opinion are more trouble than they are worth when:
a. Trying to remember the name
b. When copying Sheets to a New Workbook, I often have to delete the 'Named Ranges' and recreate them. When I do use 'Named Ranges', I always create two routines in VBA: one to create them, and one to delete them.

I prefer to use constants declared in a code module I call ModConstantsAndGlobals. This way:
a. I know where to find the value
b. When a row or column changes, it is necessary to modify the values, but they are all in one place.

For example:

Please Login or Register  to view this content.

When using the item in the code I prefer to use .Cells instead of .Range:

Please Login or Register  to view this content.

Lewis

[Croweater]

Yes, all well and good, but then, when you insert a column in your worksheet (like the OP wants) you have to change the column id in your code.

Easy enough if you know what to do but if he is writing a spreadsheet for his sales manager say, who knows nothing about VBA.....

[LJMetzger]

when you insert a column in your worksheet (like the OP wants) you have to change the column id in your code.

I can live with that limitation, which has served me well over the years.

----------------------------------

See the attached file containing the following code excerpts which uses a Scripting Dictionary to dynamically identify input and output columns.

Please Login or Register  to view this content.

Lewis

[rorya]

But if you're dealing with a Table, you can refer to the columns by name (as karmapala showed earlier) so it's all moot, no?