Variable Range/chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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).
 
Back
Top