Creating Cell Names using exact values in another cell

Me22

New Member
Joined
Feb 17, 2020
Messages
19
Office Version
  1. 2010
Platform
  1. Windows
I have a long list of cells (where input is entered) to Name, and the Names are mostly associated with labels for those cells.

Yes there is the "Create From Selection" button in the Formulas tab. In fact I just had that button pointed out to me while searching for this solution. I thought is was THE solution, too.

Unfortunately, I need a bit more control over the situation. Some of the field names are too long, and in other instance I need to customize the cell names based on the function they will play.

Because of this, I'd like to be able to specify the cell name for a number of cells, then run a macro that will name the cells.

When I try to record a macro, the macro always includes the text of the specific name, but I don't need it to that. I want it to reference a name entered in that cell:

The Maro recording makes this code: ActiveWorkbook.Names.Add Name:="Address1", RefersToR1C1:="=Sheet1!R5C9"

What I need the code to say is say something like this: ActiveWorkbook.Names.Add Name:="Value.Cell (Sheet1!A1)", RefersToR1C1:="=Sheet1!R5C9"
so it gets the name from the value of Cell A1.

However, apparently the Name parameter can't be formatted as a cell reference. But that is what I need. I need that because I need it to loop through a bunch of cells and generate the names.

here is what the sheet looks like that the code would be applied to:

1582165625175.png


So, any thoughts? Based on the example above, can VBA do this?: Name a cell, with a the specific name in a specified cell? Going one at a time will be grueling because there are around 85 cells to name in the worksheet I'm working on now... and there are others I have to change and create that are very similar. Just trying to automate the task.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
You can do it like this
VBA Code:
Sub Me22()
   Dim Cl As Range
   
   For Each Cl In Selection
      ActiveWorkbook.Names.Add Cl.Offset(, -3).Value, Cl
   Next Cl
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,204
Messages
5,623,352
Members
415,969
Latest member
Rey99

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