Names.Add

[Guest]

Having trouble with adding a named range via code using a three column list…
The sheet the first named range should adhere to is ='Data Sheet'!$I$47
What I get is ="Data Sheet'!$I$47" which is wrong.
When concatenating ‘ to varC I get “’Data Sheet'!$I$47" also not valid.
How do I resolve getting each named range bound to the their respective
sheets correctly.

Appreciatively,
Arturo

varA=_2007
varB= Data Sheet'!$I$47
varC=Col1

Sub NameAdd()
Dim myRange As Range
Dim rowZ As Integer
Dim varA As String
Dim varB As String
Dim varC As String
Dim CntrA As Integer

Set myRange = Sheets("NamedRanges").Range("A1").CurrentRegion
rowZ = myRange.Rows.Count
For CntrA = 1 To rowZ
varA = Sheets("NamedRanges").Cells(CntrA, 1).Value
varB = Sheets("NamedRanges").Cells(CntrA, 2).Value
varC = Sheets("NamedRanges").Cells(CntrA, 3).Value
ActiveWorkbook.Names.Add Name:=varC, _
RefersToR1C1:=varB
ActiveWorkbook.Names(varA).Delete
Next CntrA
End Sub

 

[Bob Phillips]

Dim myRange As Range
Dim rowZ As Integer
Dim varA As String
Dim varB As String
Dim varC As String
Dim CntrA As Integer

Set myRange = Sheets("NamedRanges").Range("A1").CurrentRegion
rowZ = myRange.Rows.Count
For CntrA = 1 To rowZ
varA = Sheets("NamedRanges").Cells(CntrA, 1).Value
varB = Sheets("NamedRanges").Cells(CntrA, 2).Value
varC = Sheets("NamedRanges").Cells(CntrA, 3).Value
ActiveWorkbook.Names.Add Name:=varC, _
RefersTo:="=" & varB
ActiveWorkbook.Names(varA).Delete
Next CntrA


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you Bob.
I made one adjustment due to an apostrophe missing from my list of varB:

ActiveWorkbook.Names.Add Name:=varC, _
RefersTo:="='" & varB