Cannot assign a range to seriecollection values

[matelot]

I use the following code to assign a range in my macro. I checked with other
webite and the syntax seems to be correct.
Activesheet.chartobjects("Chart 1").activate
set rng = activesheet.range(cells(1,1), cells(x, y))
Activechart.seriescollection(1).values = rng

x and y are variables I set earlier in the code.
The error message I get is "run time error 1004. Unable to set the values
property of the series class

Thanks for the help.

 

[Jon Peltier]

What does the .values property of SeriesCollection(1) refer to prior to
setting it to that range? If it contains all blanks or all errors, and it's
a line or XY chart, Excel VBA cannot assign anything to the series .Name,
..XValues, or .Values properties. Also, if both x and y are greater than 1,
Excel will not want to assign the range to a series. Only ranges that
contain a single cell, row, or column are valid entries.

- Jon

 

[matelot]

You are right! It doesn't work when the range is assigned to a blank range.
It's good to know. As a workaround, I load a row into an array and assign the
array into the seriecollection. That seems to work.

Thanks for letting me know about the blank range. BTW, great website to
learn about chart.

Thanks again.

 

[Jon Peltier]

It's better to ultimately have the range assigned to the series data,
especially if it's a lot of points. My usual workaround is to change the
chart type temporarily to a column chart, which plots blanks as zeros, apply
the appropriate range, then change back to the original chart type.

- Jon