Series Collection Results in Runtime Error 1004

[Matthew Cunliffe]

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
"Matthew Cunliffe" <Matthew [email protected]> wrote in
message news:[email protected]...
> 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

[Matthew Cunliffe]

"Kaak" wrote:
> 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

[Matthew Cunliffe]

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

[Matthew Cunliffe]

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.
--
Matthew Cunliffe
www.euro-point.co.uk
Test Consultancy


"Matthew Cunliffe" wrote:

> 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

[Jon Peltier]

You can do this without using code:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Matthew Cunliffe" <Matthew [email protected]> wrote in
message news:[email protected]...
> 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

[Matthew Cunliffe]

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!)
--
Matthew Cunliffe
www.euro-point.co.uk
Test Consultancy


"Jon Peltier" wrote:

> You can do this without using code:
>
> http://peltiertech.com/Excel/Charts/Dynamics.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Matthew Cunliffe" <Matthew [email protected]> wrote in
> message news:[email protected]...
> > 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
>
>
>

[Peter T]

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

Regards,
Peter T

"Matthew Cunliffe" <[email protected]> wrote in
message news:[email protected]...
> 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.
> --
> Matthew Cunliffe
> www.euro-point.co.uk
> Test Consultancy
>
>
> "Matthew Cunliffe" wrote:
>
> > 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

[Matthew Cunliffe]

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!
--
Matthew Cunliffe
www.euro-point.co.uk
Test Consultancy


"Peter T" wrote:

> Can you edit the new source data Values in the existing series manually
>
> Regards,
> Peter T
>
> "Matthew Cunliffe" <[email protected]> wrote in
> message news:[email protected]...
> > 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.
> > --
> > Matthew Cunliffe
> > www.euro-point.co.uk
> > Test Consultancy
> >
> >
> > "Matthew Cunliffe" wrote:
> >
> > > 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
>
>
>

[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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Matthew Cunliffe" <[email protected]> wrote in
message news:[email protected]...
> 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!)
> --
> Matthew Cunliffe
> www.euro-point.co.uk
> Test Consultancy
>
>
> "Jon Peltier" wrote:
>
>> You can do this without using code:
>>
>> http://peltiertech.com/Excel/Charts/Dynamics.html
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "Matthew Cunliffe" <Matthew [email protected]> wrote in
>> message news:[email protected]...
>> > 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
>>
>>
>>