Names.Add
 

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

Names.Add
 

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)

"Arturo" wrote in message
...
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

Names.Add
 

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

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

"Bob Phillips" wrote:

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)

"Arturo" wrote in message
...
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