Continuation of message re: Range Names

[Guest]

I have a macro to add range names to a workbook and the names and ranges are
listed in a worksheet. I'm able to add the names, but the range is shown as
text. Here is what I have:

Sub PPQDefineRangeName()
'
' DefineNamefromList Macro
' Macro recorded 1/16/2006 by Barb Reinhardt
'
Dim lastrow
Dim rangename
Dim seriesrange
Dim CurBook
Dim i
Dim sht

CurBook = Application.ActiveWorkbook.Name
lastrow = Worksheets("RangeName").Cells(Rows.Count, "a").End(xlUp).Row
Debug.Print lastrow
For i = 2 To lastrow
rangename = Workbooks(CurBook).Worksheets("RangeName").Range("a" &
i).Value
seriesrange = Workbooks(CurBook).Worksheets("RangeName").Range("c" &
i).Value
sht = Workbooks(CurBook).Worksheets("RangeName").Range("d" & i).Value
Debug.Print rangename; seriesrange; sht
'range name can't have a "'" or a "-" or a "%" or a " " or a "." or "("
or ")" or "+"
'Added the following
'Sheets(sht).Activate
ActiveWorkbook.Names.Add Name:=rangename, RefersToR1C1:=seriesrange
'Exit Sub
Next

End Sub

When I look at the data in the named ranges, it looks like:

="OFFSET(InterTeam-ChartLabel,4,0)"
but it should look like
=OFFSET(InterTeam-ChartLabel,4,0)

The data is displayed in the original sheet as:
OFFSET(InterTeam-ChartLabel,4,0)

How do I get the " to go away so that the ranges are useful?

Thanks in advance,
Barb Reinhardt

 

[Tom Ogilvy]

As previously shown:

ActiveWorkbook.Names.Add Name:=rangename, RefersToR1C1:=seriesrange

should be:

ActiveWorkbook.Names.Add Name:=rangename, RefersToR1C1:="=" &seriesrange

 

[Tom Ogilvy]

I see there is some traffic in the old thread. Here is the answer I posted
there:

Don't use ReferstoR1C1 when you are using A1 addressing. Also, append an
equal sign

RefersToR1C1:=seriesrange

should be

RefersTo:="=" & seriesrange

make sure ampersands are separated on each side with a space.

 

[Guest]

I tried that before and it didn't work ... but I did find that I had to tweak
the info in the worksheet. I'll try it again.

 

[Guest]

Tom,

I tried what you suggested and got the same thing.

There are "" around every range that has OFFSET in it. And I have about 60
of them.

 

[Guest]

Tom ... I just realized that some are correct and some aren't. I think I
need to delete all of the names and start from scratch.

Barb

 

[Tom Ogilvy]

As a test, open a new workbook and run this code:

Sub ABCTest()
ActiveWorkbook.Names.Add Name:="Name2", _
RefersTo:="Sheet1!$A$1:$A$10"
' Mix A1/R1C1 and omit equal sign
ActiveWorkbook.Names.Add Name:="Name3", _
RefersToR1C1:="Sheet1!$A$1:$A$10"
' Should work
ActiveWorkbook.Names.Add Name:="Name4", _
RefersTo:="=Sheet1!$A$1:$A$10"
End Sub

Name2 and Name3 will exhibit the behavior you describe (with quotes). Name4
works correctly

I had this initially
' Mix A1 and R1C1
ActiveWorkbook.Names.Add Name:="Name1", _
RefersToR1C1:="=Sheet1!$A$1:$A$10"

But it raised an error although it seems to me in earlier versions this
caused the same problem.