VBA to Define Names for columns

I want to be able to define names for multiple columns by inserting the formula "=INDEX(Table,,"Variable") in the Refers to box in the New Name dialogue box.
I used the macro recorder to get part of the way

'Range("Table1[[#Headers],[_fd_id]]").Select
'ActiveWorkbook.Names.Add Name:="_fd_id", RefersToR1C1:="=INDEX(Table,,1)"
'ActiveWorkbook.Names("_fd_id").Comment = ""

I then wrote the code below

Sub DefineNameForColumns()

FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To FinalCol


Cells(2, i).Select
ActiveWorkbook.Names.Add Name:=Cells(2, i).Value, RefersToR1C1:="=INDEX(Table,,i)"

'ActiveWorkbook.Names("_fd_id").Comment = ""

Next i

How do I change the code to get the variable "i" expressed in "=INDEX(Table,,i)"

Thanks

Steve Excel 2013

Please Login or Register  to view this content.

Regards, TMS

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

What is the Activesheet name?

Can you post a small sample workbook? Not sure how your worksheets and data are structured so it will help to put the code into context.

FOR EXCEL FORUM.xlsx
I attached a spreadsheet - hopefully this will make things clearer

Steve

At the risk of stating the very obvious a quick look in the name manager should help make clearer

OK, now I can see what the worksheet and table look like, I have to ask, why do you need to create all those Named Ranges?

As it's a Structured Table, you have a number of built in references, so you don't really need to try to create Dynamic Named Ranges

Please Login or Register  to view this content.

I could, for example, create a Named Range, nrTMS:

Formula:

Please Login or Register  to view this content.

Then I can refer to the Dynamic Named Range, nrTMS, which Excel will manage for me:

Please Login or Register  to view this content.

So, if you really want to create your own named ranges, you could build on that approach ... or you could not bother re-inventing the wheel and just use the gifts that Excel provides.


Regards, TMS

This:

Formula:

Please Login or Register  to view this content.

could be, for example:

Formula:

Please Login or Register  to view this content.

And this:

Formula:

Please Login or Register  to view this content.

could be this:

Formula:

Please Login or Register  to view this content.

Regards, TMS

You can also use the Structured Table references in Conditional Formatting. For example, you could use

Formula:

Please Login or Register  to view this content.

applied to:

Formula:

Please Login or Register  to view this content.

to highlight duplicate surnames in column H.

Bit messy, of course, but it works well.

Or, strictly:

Formula:

Please Login or Register  to view this content.

I would expect it to be something like:

Please Login or Register  to view this content.

In testing, I have a table, Table1, on a worksheet, Table1, which is the ActiveSheet. Hence ActiveSheet.Name is Table1.

Both Name additions give exactly the same result for me.


Regards, TMS

Good ... But how is that different to the code I posted two hours previously?

And how is it different to what you had in post #5?

You're welcome.