Chart update using VBA

[Nigel]

I have some code that set the range for two series charts on one worksheet.

The charts are embedded on a worksheet, and the ranges xV, xX and xT are set
within the code and are valid references pointing to the data required.

My problem is I get 'Unable to set the values property of the series class'
error when setting SeriesCollection(2), collection(1) works OK, I have tried
setting SeriesCollection(2) to the range xV which I know works for
SeriesCollection(1), so nothing wrong with the range. SeriesCollection(2)
does exist in the chart and if I set the chart data range manually it works
OK!

I am obviously not applying the range correctly - please help.

Dim mCh as ChartObject
Dim xV as Range, xX as Range, xT as Range

For Each mCh in ChartObjects

' code that sets xV, xX and xT removed for clarity

mCh.Activate
ActiveChart.SeriesCollection(1).Values = xV
ActiveChart.SeriesCollection(1).XValues = xX
ActiveChart.SeriesCollection(2).Values = xT '<==== error on this line

Next

 

[Peter T]

Assuming xT refers to a suitable range in a single row or column of cells,
and the chart contains two series, maybe the data when converted to an array
like this
"{1 ,2, 3}"
is more then 255 characters incl. brackets, commas & spaces

Normally it's more convenient to link to data in cells, you could re-write
the series formula or try this
s = "=" & xT.Address(, , xlR1C1, True)
ActiveChart.SeriesCollection(2).Values = s

Regards,
Peter T

 

[Jon Peltier]

That's not it; it worked for series 1. If xT is actually defined as a valid
range, then .Values = xT should set the values to the range, without needing
the intermediate formula construction.

What's the chart type? If it's a line or XY chart, does series 2 have any
valid data in its .Values property to begin with? If not, you'll get this
error. You must either make sure that the series already has valid data
(i.e., points plotted), or convert the series temporarily to another type
(e.g., area or column).

- Jon

 

[Nigel]

hi Jon

If I renew the xValues first it works!

ActiveChart.SeriesCollection(1).XValues = xX
ActiveChart.SeriesCollection(1).Values = xV
ActiveChart.SeriesCollection(2).Values = xT

Curious!

Thanks for your help
--

Regards,
Nigel
(e-mail address removed)

 

[Jon Peltier]

It's more of an art than a science.

- Jon