Series Collection Results in Runtime Error 1004

[Guest]

Hello,

I have an existing chart on a worksheet for which I am trying to update the
XValues for each series. The update works for the first series that I want
to change, but on the subsequent ones, it raises a Runtime Error 1004. The
original values in each series are all valid. If I swap the series around
(so that the second and third are before the first) then the error occurs
immediately.

Below is a cut-down version of the code I am using. The startrow and endrow
variables are normally populated from elsewhere.

If anyone could give me a pointer as to why it works for the first series
but fails on all subsequent ones I would be grateful.

Set CHT = Worksheets("Chart").ChartObjects("Chart 1").Chart

startrow = 152
endrow = 196

Value1 = "='Overall Progress - Tabular'!R" & startrow & "C6:R" & endrow & "C6"
Value2 = "='Overall Progress - Tabular'!R" & startrow & "C8:R" & endrow & "C8"
Value3 = "='Overall Progress - Tabular'!R" & startrow & "C10:R" & endrow &
"C10"

CHT.SeriesCollection("Cumulative Actual").XValues= Value1
CHT.SeriesCollection("Cumulative Predictive Failed").XValues = Value2
CHT.SeriesCollection("Cumulative Actual Failed").XValues = Value3

 

[Kaak]

Try

CHT.SeriesCollection("Cumulative Actual").Values= Value1
CHT.SeriesCollection("Cumulative Predictive Failed").Values = Value2
CHT.SeriesCollection("Cumulative Actual Failed").Values = Value3

The XValues property is for the scale

 

[Peter T]

I replicated your code and didn't get an error (having first ensure the
chart contained the three named series). But are you sure you want to update
each series with different XValues. I changed all your .XValues to .Values
(ie on the Y axis) and added two more lines

Value4 = "='Sheet1'!R" & startrow & "C5:R" & endrow & "C5"
CHT.SeriesCollection("Cumulative Actual").XValues = Value4

With category X labels in col-5 it worked and seemed to make sense.

Regards,
Peter T

With

 

[Guest]

Try

CHT.SeriesCollection("Cumulative Actual").Values= Value1
CHT.SeriesCollection("Cumulative Predictive Failed").Values = Value2
CHT.SeriesCollection("Cumulative Actual Failed").Values = Value3

The XValues property is for the scale

Thanks. I originally did use Values (the help file wasn't clear as to the
difference) but the same fault occurs whichever I use.

Matthew

 

[Guest]

Peter,

Thanks for your reply. I've had a go (changing my XValues back to Values
which I had originally used) and adding the X-axis, but I'm still getting the
same error. Based on the fact that you didn't have any problems, I'm
starting to wonder if the chart is corrupt. I'll recreate it as a new chart
and see what happens...

Matthew

 

[Guest]

As an update, I've removed all charts from the workbook, and inserted a new
one with the same setup as before. Unfortunately, despite this, the code
still falls over on the second value when trying to update that series.

 

[Jon Peltier]

You can do this without using code:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[Guest]

Thanks Jon. I need to write it in code as it is part of an automated update
to the whole workbook. The idea is that the user just presses one button,
all the data is collated and processed, then displayed in the updated chart.
As this is likely to occur several times a day, it needs to be fairly rapid
(although I seem to have spend half a day trying to resolve this!)

 

[Peter T]

Can you edit the new source data Values in the existing series manually

Regards,
Peter T

 

[Guest]

Peter,

Yes, I can. It turns out that removing the charts and starting from scratch
did do the trick, but I introduced another error when I was retyping the
code, so I thought I hadn't resolved it.

The solution appears to be that it looks like the chart was corrupt.
Thanks for all your help!

 

[Jon Peltier]

Dynamic charts made in this way are in general as rapid as VBA, since they
rely on built in Excel worksheet functions. As soon as the VBA changes the
input data, the dynamic formulas update themselves, and the chart keeps
pace.

- Jon