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