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

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 ***

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

I would have thought you would need to do:

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

if that is what you wanted to do.

--
Regards,
Tom Ogilvy

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

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(ActI tms!$33:$33)))

The error is on the following line:

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

"Tom Ogilvy" wrote:

I would have thought you would need to do:

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

if that is what you wanted to do.

--
Regards,
Tom Ogilvy

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

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

"Barb Reinhardt" wrote:

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(ActI tms!$33:$33)))

The error is on the following line:

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

"Tom Ogilvy" wrote:

I would have thought you would need to do:

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

if that is what you wanted to do.

--
Regards,
Tom Ogilvy

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