"Dynamic?" Named Ranges

pkhandique

New Member
Joined
Jul 29, 2010
Messages
4
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
Welcome to the Board!

This is one way:

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

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
hi, and welcome to MrExcel

try this:
Code:
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

New Member
Joined
Jul 29, 2010
Messages
4
Thanks to both of you for your replies. I tried schielrn's code and it worked like a charm. Thanks a lot.

Pankaj
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887

ADVERTISEMENT

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

Active Member
Joined
Aug 29, 2008
Messages
336
hi, and welcome to MrExcel

try this:
Code:
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,941

ADVERTISEMENT

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? :pray:

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

New Member
Joined
Jul 29, 2010
Messages
4
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,111
Messages
5,857,448
Members
431,880
Latest member
kar2rost

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