Solved: Insert New Range Name

[skulakowski]

I'm trying to cycle through a handful of ranges to insert new range names based on the name of the current tab.

The string variables work. The re-positioning works. But my code inserts the first range name in the very last position. It does not insert any other name.

Can anybody tell me what I'm doing wrong?



Dim ThisTab As String
ThisTab = Range("AK1").Value

Dim n As Integer
n = 1 'cycles through to 5

Dim NameMe As String
Dim Refers As String


NameMe = ThisTab & n & "_"
Refers = ThisTab & "!R3C29"
Application.Goto Reference:=Refers
ActiveWorkbook.Names.Add Name:=NameMe, RefersToR1C1:=ActiveCell
'these don't work either:
'ActiveWorkbook.Names.Add Name:=NameMe, RefersToR1C1:= "=" & ActiveCell
'ActiveWorkbook.Names.Add Name:=NameMe, RefersToR1C1:= "=" & Refers

ActiveWorkbook.Names(NameMe).Comment = ""

[p45cal]

untested: after the Goto line:
Activecell.name = NameMe
you probably don't need the Refers variable.

[skulakowski]

Sadly, ActiveCell.Name = NameMe doesn't work.

[p45cal]

Sadly, ActiveCell.Name = NameMe doesn't work.
'Doesn't work' doesn't give me much to go on.

[skulakowski]

I wish I could be more specific and identify an error but there is none. The code runs completely but doesn't add any range name.

The problem appears to be with using NameMe as a variable, not a string enclosed in quotes, in Names.Add function
ActiveWorkbook.Names.Add Name:=NameMe
If I hardcode any string, i.e., "NameHere" with quotes, the code inserts the range name. So I think I need to add a quote, before and after, as part of the NameMe string.

To incorporate the beginning/ending quote, I tried redefining NameMe as
NameMe = Chr(34) & ThisTab & n & "_" & Chr(34)

but this gives me a run-time 1004 error, "the name you entered is not valid". Among the suggested reasons are "name begins with an invalid character".

[mikerickson]

I think that this might work for you
ActiveWorkbook.Names.Add Name:=NameMe, RefersTo:= "=" & ActiveCell.Address(,,,True)

However the whole thing could be replaced with
Dim ThisTab As String
ThisTab = Range("AK1").Value

Dim n As Integer
n = 1 'cycles through to 5

Dim NameMe As String

NameMe = ThisTab & n & "_"

ThisWorkbook.Sheets("ThisTab").Cells(3, 29) = NameMe

ActiveWorkbook.Names(NameMe).Comment = ""

The idea of n cycling from 1 to 5 is a bit confusing. Do you want ThisTab!AC3 to have 5 names?

RE:"The code runs completely".
That's odd, Comment is not a method of a Name object, I would expect that line to error. It did in my testing.
If this code is inside an On Error Resume Next section, removing that might help track down the error.

[skulakowski]

Based on this Microsoft article, How to programmatically copy all range names in Excel (http://support.microsoft.com/kb/213389) (http://support.microsoft.com/kb/213389%29), I've discovered that writing the NameMe string to a cell + resetting the value of NameMe as the cell contents works.

Refers = ThisTab & "!R3C29"
NameMe = ThisTab & n & "_"
Range("a1").Value = NameMe
NameMe = Range("a1").Value

ActiveWorkbook.Names.Add Name:=NameMe, RefersToR1C1:="=" & Refers

Seems inelegant to me but this 'trick' of writing out and resetting NameMe does indeed insert new range names.

[skulakowski]

final code
Dim ThisTab As String
ThisTab = Range("AK1").Value

Dim NameMe As String
Dim Refers As String

Dim n As Integer
Dim c As Integer
Dim r As Integer
r = 3

For n = 1 To 5

c = 29
NameMe = ThisTab & n & "_"
Refers = ThisTab & "!R" & r & "C" & c
Range("a1").Value = NameMe
NameMe = Range("a1").Value
ActiveWorkbook.Names.Add Name:=NameMe, RefersToR1C1:="=" & Refers

c = 30
NameMe = ThisTab & n & "1_"
Refers = ThisTab & "!R" & r & "C" & c
Range("a1").Value = NameMe
NameMe = Range("a1").Value
ActiveWorkbook.Names.Add Name:=NameMe, RefersToR1C1:="=" & Refers

r = r + 10

Next n

Range("a1).Value = ""