Variable Range/chart
 

I have a program that will generate a chart using a variable range up to a
length of 17 cells. If the length is 18 or greater, I get a "Run-time error
'1004': Unable to set the values property of the series class."

My goal is to be able to generate a chart for varying cell lengths (like a
range of 150 to 200 cells).

What is the best way to do so, and why does the program as written only work
for
sstop < 17?

Sub dynamicrangetest()

Dim vrange As Variant
Dim irange As Variant

Dim cht As chart
Dim senewseries As Series

sstart = 4
vcol = 3
icol = 4
sstop = 204

irange = Range(Cells(sstart, icol), Cells(sstop, icol)).Value
vrange = Range(Cells(sstart, vcol), Cells(sstop, vcol)).Value
Set cht = Charts.Add

With cht
.ChartType = xlLine
Set senewseries = .SeriesCollection.NewSeries
senewseries.Values = irange
senewseries.xvalues = vrange

End With

End Sub

Thank you in advance for any suggestions.

Variable Range/chart
 

create one with 17 cells.

then manually go into the chart and select the series. Look up in the
formula bar. I suspect the formula will be approaching the string length
limit for a formula.

You will probably need to put your array in a defined name (equivalent to
Insert=Name=Define) and then assign that defined name as the source for
your series.

A defined name will work as long as you don't try to look at it manually (it
can hold a large array).

--
Regards,
Tom Ogilvy


"ballyb" wrote:

I have a program that will generate a chart using a variable range up to a
length of 17 cells. If the length is 18 or greater, I get a "Run-time error
'1004': Unable to set the values property of the series class."

My goal is to be able to generate a chart for varying cell lengths (like a
range of 150 to 200 cells).

What is the best way to do so, and why does the program as written only work
for
sstop < 17?

Sub dynamicrangetest()

Dim vrange As Variant
Dim irange As Variant

Dim cht As chart
Dim senewseries As Series

sstart = 4
vcol = 3
icol = 4
sstop = 204

irange = Range(Cells(sstart, icol), Cells(sstop, icol)).Value
vrange = Range(Cells(sstart, vcol), Cells(sstop, vcol)).Value
Set cht = Charts.Add

With cht
.ChartType = xlLine
Set senewseries = .SeriesCollection.NewSeries
senewseries.Values = irange
senewseries.xvalues = vrange

End With

End Sub

Thank you in advance for any suggestions.