Adding Range Names

[Barb Reinhardt]

I'm wanting to add range names from a list in a spreadsheet. I have several
questions:

I have this code so far ...

Dim lastrow
Dim rangename
Dim seriesrange

CurBook = Application.ActiveWorkbook.Name
lastrow = Worksheets("SeriesList").Cells(Rows.Count, "i").End(xlUp).Row
For i = 2 To lastrow
Workbooks(CurBook).Worksheets("SeriesList").Range("i" & i).Value =
rangename
Workbooks(CurBook).Worksheets("SeriesList").Range("h" & i).Value =
seriesrange
ActiveWorkbook.Names.Add Name:=rangename, RefersToR1C1:=seriesrange
Next

End Sub

Question 1: What format should the rangename and seriesname be in the
spreadsheet. I currently have something that looks like this for the series
name

Name_ChartLabel (but it's calculated from other fields)
SeriesRange is listed in the workbook as
=OFFSET(K11,J11,0) where K11 is a label, J11 is the offset in rows.

What do I need to change so that this will work properly?

Thanks in advance,

Barb Reinhardt

 

[Edward Ulle]

Barb,

An Excel object name is simply a string or text. For example
ActiveWorkbook.Range("I2:I100").Name = "MyRange".

First I would suggest including Option Explicit at the beginning of
every code module to insure variable types are declared and consistent.
For example Dim rangename As String.

Your macro doesn't show where rangename and seriesrange come from so I'm
assuming you have your equations transposed.

rangename = WorkbooksCurBook).Worksheets"SeriesList").Range("i" &
i).Value
serierange = WorkbookCurBook).Worksheets"SeriesList").Range("h" &
i).Value

Do you really want every cell to have a unique name?




*** Sent via Developersdex http://www.developersdex.com ***

 

[Barb Reinhardt]

I now have the following:

Dim lastrow
Dim rangename
Dim seriesrange
Dim CurBook
Dim i

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("b" &
i).Value
Debug.Print rangename; seriesrange
ActiveWorkbook.Names.Add Name:="=" & rangename, RefersToR1C1:=seriesrange
Next

A2: chtlen
B2: '=12

Thanks,
Barb

 

[Tom Ogilvy]

I would have thought you would need to do:

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

if that is what you wanted to do.

 

[Barb Reinhardt]

I'm now getting a runtime error:
A B
2 chtlen =12
3 ActItms_ChartLabel
=OFFSET(ActItms!$B$33,0,0,1,min(chtlen,COUNTA(ActItms!$33:$33)))

The error is on the following line:

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

 

[Barb Reinhardt]

Let me clarify this:
A2:chtlen
B2:=12
A3:ActItms_ChartLabel
B3:=OFFSET(ActItms!$B$33,0,0,1,min(chtlen,COUNTA(ActItms!$33:$33)))

 

[Tom Ogilvy]

All I can do is demonstrate the mechanics in the immediate window, which
worked fine for me:

rangename = "chtlen"
seriesrange = 12
ActiveWorkbook.Names.Add Name:= rangename, RefersToR1C1:="=" & seriesrange
? activeworkbook.Names(rangename).Name
chtlen
? activeworkbook.Names(rangename).RefersTo
=12
Activeworkbook.Names.Add Name:="ActItms_ChartLabel", _

Refersto:="=OFFSET(ActItms!$B$33,0,0,1,min(chtlen,COUNTA(ActItms!$33:$33)))"
? activeworkbook.Names("ActItms_ChartLabel").Name
ActItms_ChartLabel
?
activeworkbook.Names("ActItms_ChartLabel").RefersToRange.Address(0,0,xlA1,Tr
ue)
[Book1]ActItms!B33:M33

 

[Barb Reinhardt]

Tom,

I've figured out the problem. Your responses were very helpful in doing
that. Thanks,

Barb Reinhardt

All I can do is demonstrate the mechanics in the immediate window, which
worked fine for me:

rangename = "chtlen"
seriesrange = 12
ActiveWorkbook.Names.Add Name:= rangename, RefersToR1C1:="=" & seriesrange
? activeworkbook.Names(rangename).Name
chtlen
? activeworkbook.Names(rangename).RefersTo
=12
Activeworkbook.Names.Add Name:="ActItms_ChartLabel", _

Refersto:="=OFFSET(ActItms!$B$33,0,0,1,min(chtlen,COUNTA(ActItms!$33:$33)))"
? activeworkbook.Names("ActItms_ChartLabel").Name
ActItms_ChartLabel
?
activeworkbook.Names("ActItms_ChartLabel").RefersToRange.Address(0,0,xlA1,Tr
ue)
[Book1]ActItms!B33:M33

--
Regards,
Tom Ogilvy

Let me clarify this:
A2:chtlen
B2:=12
A3:ActItms_ChartLabel
B3:=OFFSET(ActItms!$B$33,0,0,1,min(chtlen,COUNTA(ActItms!$33:$33)))

 

[Barb Reinhardt]

Tom,

I now have this as my code:

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 for possible use later
'Sheets(sht).Activate
ActiveWorkbook.Names.Add Name:=rangename, RefersToR1C1:=seriesrange
'Exit Sub
Next

My problem is that when I look at the range names, they look like this:
="OFFSET(ActItms!$B$33,0,0,1,min(chtlen,COUNTA(ActItms!$33:$33)))"
instead of
=OFFSET(ActItms!$B$33,0,0,1,min(chtlen,COUNTA(ActItms!$33:$33)))

What do I need to change? I assume it's something in the
ActiveWorkbook.Names line, but am not sure what.

Thanks,
Barb

 

[Tom Ogilvy]

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.

 

[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.