I have the following snippet of code where I am trying to update a
series in my chart:

************************************************** **
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = MySeriesString
************************************************** **

In a large amount of previous code I have set MySeriesString to the
following in order to pick the exact cells I want in the plot:

"Results!D6,Results!D10,Results!D14,Results!D18,Re sults!D22,Results!D26,Results!D30"

But when I get to the line where I set the series values to
MySeriesString I get an error:

"Unable to set the Values property of the Series class"

I assume I just don't understand how to format the MySeriesString. I
have tried many combinations of putting an "=" sign and brackets around
the data, but I can't seem to figure it out. Can anyone help me with
that?

Thanks,
Matt

Matt,

It sounds like maybe your MySeriesString array is not loading correctly.
Here's an example of code that loads a discontiguous range of X and Y axis
data into arrays. It then updates the chart with that data. The example
assumes that your X-axis data is in cell A1, A3, A5, A7, and A9. The Y-axis
data is in cell B1, B3, B5, B7, and B9. To see what's happening with your
MySeriesString array, look how I've set up the array called XArray and
YArray.

The ReDim Preserve statements allow the XArray and YArray to expand with
additional data as the cells are looped through. I think this is where you
might need to update your code that generates the MySeriesString array.

Sub UpdateChart()

Dim XArray()
Dim YArray()
Dim Rng As Range
Dim Ctr As Integer

Ctr = 0

For Each Rng In Range("A1,A3,A5,A7,A9")

ReDim Preserve XArray(Ctr)
ReDim Preserve YArray(Ctr)

XArray(Ctr) = Rng.Value
YArray(Ctr) = Rng.Offset(0, 1).Value

Ctr = Ctr + 1

Next Rng

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).XValues = XArray
ActiveChart.SeriesCollection(1).Values = YArray

End Sub

----
Regards,
John Mansfield
http://www.pdbook.com


" wrote:

I have the following snippet of code where I am trying to update a
series in my chart:

************************************************** **
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = MySeriesString
************************************************** **

In a large amount of previous code I have set MySeriesString to the
following in order to pick the exact cells I want in the plot:

"Results!D6,Results!D10,Results!D14,Results!D18,Re sults!D22,Results!D26,Results!D30"

But when I get to the line where I set the series values to
MySeriesString I get an error:

"Unable to set the Values property of the Series class"

I assume I just don't understand how to format the MySeriesString. I
have tried many combinations of putting an "=" sign and brackets around
the data, but I can't seem to figure it out. Can anyone help me with
that?

Thanks,
Matt

John,

Thanks for the response. You code has actually give me some good
ideas. The only difference between your code and what I need to do is
that your code actually puts the actual values into the
SeriesCollection(1).XValues/SeriesCollection(1).Values. I need to put
the cell references in there instead. So instead of putting the values
of A1, A3, A5, A7, A9...I need to put the cells A1, A3, A5, A7, A9 in
the graph so that if the values of these cells change the tables will
automatically be updated.

I think I am just having a formatting issue with "MySeriesString".

I have tried different formatting things like:

MySeriesString = "=D6,D10,D14"
MySeriesString = "=Results!D6,Results!D10,Results!D14"
MySeriesString = "Results!D6,Results!D10,Results!D14"
MySeriesString = "=(Results!D6,Results!D10,Results!D14)"

and setting that to the chart like this:

ActiveChart.SeriesCollection(1).Values = MySeriesString

But I always seem to get "Unable to set the Values property of the
Series class".

Thanks,
Matt

John Mansfield wrote:
Matt,

It sounds like maybe your MySeriesString array is not loading
correctly.
Here's an example of code that loads a discontiguous range of X and Y
axis
data into arrays. It then updates the chart with that data. The
example
assumes that your X-axis data is in cell A1, A3, A5, A7, and A9. The
Y-axis
data is in cell B1, B3, B5, B7, and B9. To see what's happening with
your
MySeriesString array, look how I've set up the array called XArray
and
YArray.

The ReDim Preserve statements allow the XArray and YArray to expand
with
additional data as the cells are looped through. I think this is
where you
might need to update your code that generates the MySeriesString
array.

Sub UpdateChart()

Dim XArray()
Dim YArray()
Dim Rng As Range
Dim Ctr As Integer

Ctr = 0

For Each Rng In Range("A1,A3,A5,A7,A9")

ReDim Preserve XArray(Ctr)
ReDim Preserve YArray(Ctr)

XArray(Ctr) = Rng.Value
YArray(Ctr) = Rng.Offset(0, 1).Value

Ctr = Ctr + 1

Next Rng

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).XValues = XArray
ActiveChart.SeriesCollection(1).Values = YArray

End Sub

----
Regards,
John Mansfield
http://www.pdbook.com


" wrote:

I have the following snippet of code where I am trying to update a
series in my chart:

************************************************** **
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = MySeriesString
************************************************** **

In a large amount of previous code I have set MySeriesString to the
following in order to pick the exact cells I want in the plot:


"Results!D6,Results!D10,Results!D14,Results!D18,Re sults!D22,Results!D26,Results!D30"

But when I get to the line where I set the series values to
MySeriesString I get an error:

"Unable to set the Values property of the Series class"

I assume I just don't understand how to format the MySeriesString.
I
have tried many combinations of putting an "=" sign and brackets
around
the data, but I can't seem to figure it out. Can anyone help me
with
that?

Thanks,
Matt

Matt -

Sometimes the chart doesn't seem as smart as the worksheeet.

Both of these (from the Immediate window) work as expected, selecting
the indicated range:

range("F7:F9").Select
range("F7,F9,F11").Select

The following successfully apply the values to the chart (selection is a
series):

selection.values = "{1,2,3}"
selection.values = range("F7:F9")

The second one applies the cell references, not just the values.

This does not work:

selection.values = range("F7,F9,F11")

I also went through the whole range of variations you did, to no avail.

But then I remembered an old trick. I set a VBA range variable to this
range:

Set MyRange = Range("F7,F9,F11")

defined a name based on it:

MyRange.Name = "MY_RANGE"

and finally used the defined name for the chart data:

Selection.Values = "=Sheet1!MY_RANGE"

The entire procedu

Sub DiscontiguousSourceData()
Dim MyRange As Range
Dim MyChart As Chart

Set MyRange = ActiveSheet.Range("F7,F9,F11")
MyRange.Name = "MY_RANGE"
Set MyChart = ActiveSheet.ChartObjects(1)
MyChart.SeriesCollection(1).Values = "=Sheet1!MY_RANGE"
End Sub

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

wrote:

John,

Thanks for the response. You code has actually give me some good
ideas. The only difference between your code and what I need to do is
that your code actually puts the actual values into the
SeriesCollection(1).XValues/SeriesCollection(1).Values. I need to put
the cell references in there instead. So instead of putting the values
of A1, A3, A5, A7, A9...I need to put the cells A1, A3, A5, A7, A9 in
the graph so that if the values of these cells change the tables will
automatically be updated.

I think I am just having a formatting issue with "MySeriesString".

I have tried different formatting things like:

MySeriesString = "=D6,D10,D14"
MySeriesString = "=Results!D6,Results!D10,Results!D14"
MySeriesString = "Results!D6,Results!D10,Results!D14"
MySeriesString = "=(Results!D6,Results!D10,Results!D14)"

and setting that to the chart like this:

ActiveChart.SeriesCollection(1).Values = MySeriesString

But I always seem to get "Unable to set the Values property of the
Series class".

Thanks,
Matt

John Mansfield wrote:

Matt,

It sounds like maybe your MySeriesString array is not loading

correctly.

Here's an example of code that loads a discontiguous range of X and Y

axis

data into arrays. It then updates the chart with that data. The

example

assumes that your X-axis data is in cell A1, A3, A5, A7, and A9. The

Y-axis

data is in cell B1, B3, B5, B7, and B9. To see what's happening with

your

MySeriesString array, look how I've set up the array called XArray

and

YArray.

The ReDim Preserve statements allow the XArray and YArray to expand

with

additional data as the cells are looped through. I think this is

where you

might need to update your code that generates the MySeriesString

array.

Sub UpdateChart()

Dim XArray()
Dim YArray()
Dim Rng As Range
Dim Ctr As Integer

Ctr = 0

For Each Rng In Range("A1,A3,A5,A7,A9")

ReDim Preserve XArray(Ctr)
ReDim Preserve YArray(Ctr)

XArray(Ctr) = Rng.Value
YArray(Ctr) = Rng.Offset(0, 1).Value

Ctr = Ctr + 1

Next Rng

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).XValues = XArray
ActiveChart.SeriesCollection(1).Values = YArray

End Sub

----
Regards,
John Mansfield
http://www.pdbook.com


" wrote:


I have the following snippet of code where I am trying to update a
series in my chart:

*********************************************** *****
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = MySeriesString
*********************************************** *****

In a large amount of previous code I have set MySeriesString to the
following in order to pick the exact cells I want in the plot:



"Results!D6,Results!D10,Results!D14,Results!D18,Re sults!D22,Results!D26,Results!D30"

But when I get to the line where I set the series values to
MySeriesString I get an error:

"Unable to set the Values property of the Series class"

I assume I just don't understand how to format the MySeriesString.

I

have tried many combinations of putting an "=" sign and brackets

around

the data, but I can't seem to figure it out. Can anyone help me

with

that?

Thanks,
Matt

Jon,

That works great.

Thanks alot,
Matt