"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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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
 
Upvote 0
Thanks to both of you for your replies. I tried schielrn's code and it worked like a charm. Thanks a lot.

Pankaj
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,382
Messages
6,136,239
Members
450,000
Latest member
jgp19

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
Back
Top