Creating Cell Names using exact values in another cell

[Me22]

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:

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.

 

[Fluff]

You can do it like this

Sub Me22()
   Dim Cl As Range
   
   For Each Cl In Selection
      ActiveWorkbook.Names.Add Cl.Offset(, -3).Value, Cl
   Next Cl
End Sub

 

[Me22]

Perfect! Thank you

 

[Fluff]

You're welcome & thanks for the feedback