"Dynamic?" Named Ranges

[pkhandique]

Hi,
I am trying to automate the process of naming each cell in a table of values by a distinct name in another table. Is this possible to do this using a macro instead of manually typing in about a hundred name.
For eg. i want cell B1 to have the name "jack", cell B2 to have the name "john" and so on. ay help will be much appreciated.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Col A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Col B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Row 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>jack</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Row 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>john</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>21</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Row 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>james</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>31</TD></TR></TBODY></TABLE>

Thanks,

Pankaj

 

[schielrn]

Welcome to the Board!

This is one way:

Sub nameCells()
For Each c In Range("B1:B3")
    c.Name = c.Offset(0, -1).Value
Next c
End Sub

Hope that helps.

 

[sulakvea]

hi, and welcome to MrExcel

try this:

Sub namerange()
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
 ThisWorkbook.Names.Add Name:=Sheet1.Cells(i, 1), RefersTo:=Sheet1.Cells(i, 2)
Next i
End Sub

 

[pkhandique]

Thanks to both of you for your replies. I tried schielrn's code and it worked like a charm. Thanks a lot.

Pankaj

 

[pgc01]

Hi Pankaj
Welcomr to the board

Excel lets you create the names directly. Just select A1:B3 and use "Create from Selection", tick the "Left" checkbox.

 

[gsbelbin]

hi, and welcome to MrExcel

try this:

Sub namerange()
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
 ThisWorkbook.Names.Add Name:=Sheet1.Cells(i, 1), RefersTo:=Sheet1.Cells(i, 2)
Next i
End Sub

You can do this by just selecting the whole table and clicking Insert | Name | Create... and then checking the box entitled "Left Column"

 

[schielrn]

Hi Pankaj

Excel lets you create the names directly. Just select A1:B3 and use "Create from Selection", tick the "Left" checkbox.

Very nice I had never noticed that feature before. Was this also available pre-2007? ray:

Looks like my pre-2007 question was answered here:

You can do this by just selecting the whole table and clicking Insert | Name | Create... and then checking the box entitled "Left Column"

 

[pkhandique]

yes i tried that. but the problem was that i have a 24*6 table and needed something that can be done automatically without changing the table structure.

Pankaj

 

[pgc01]

Hi schielrn

Yes, I remember using it in Excel 2000.

Anyway it seems that Pankaj's problem is more complex and so a code solution like yours or sulakvea's will be the solution.

 

[pkhandique]

Thanks to all you guys. my problem is now resolved. Thanks again.