VBA to change Source Data for 1 series

[MrExcel]

I have a chart with three series.

Series 1 runs from A3:M3
Series 3 runs from A5:M5

Series 2 has to run from A4.Resize(1, x) where x changes each month.

What is the code to change just that one series?
SetSourceData affects the whole chart.

I am here in a room with only Excel 2007, where of course the macro recorder returns nothing.
Thanks in Advance!

Bill

 

[sijpie]

This is the code the macro recorder produced when I changed the source for one range of a graph:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16/11/2009 by Jaap
'
'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!R8C5:R10C5"
End Sub

Does that help?

 

[Richard Schollar]

Not really adding anything here to what sijpie said but you should be able to amend the series using:

Activechart.SeriesCollection(2).Value = Range("A4").ReSize(,x)

 

[MrExcel]

Thanks.

That isn't quite doing it.

There are three series.
Series 1 is a column chart and always wants to show 12 months
Series 2 & 3 are line charts.
I want series 3 to show 12 points, but series 2 to only show the active point

(For a variety of reasons, we can not code #N/A into the future cells...)

Bill

 

[MrExcel]

Richard...
Yes - that did it. Excellent. Thanks very much.

Bill