I have a problem with changing the data source for a chart series.
I'm using a VBA program to input data to a database (really just a
worksheet) then filter, select and manipulate the data, and graph the
end results.

I currently have a scatter chart which I've only got a single series
on. There is also a 3-point moving average trendline and Y error bars
(linked to a range in the worksheet). Changing the data source for
this has been easy with the following:

Charts(1).SetSourceData Source:=Range("Dataset!AH3:AI28")

Fine. Great. Now I've added a second series to the chart, and I
can't figure out how to tell the computer to change the data source
for series 1 to X:Y and change the data source to series 2 to A:B.

I've tried:

Charts(1).SeriesCollection(1).SetSourceData
Source:=Range("Dataset!AH3:AI28")

But that crashes with "object doesn't support this property or
method".

Charts(1).SeriesCollection(1).Source = Range("Dataset!AH3:AI28")

has the same problem. Intuitively it should work, but obviously I've
got the syntax horribly wrong! I then recorded a macro of me changing
the values manually and got the code:

ActiveChart.SeriesCollection(1).XValues = "=Dataset!AH3C33:AH28C33"

But for some reason when I run that in the program that says: "Error
1004: unable to set the Xvalues property of the series class". I
cannot see where this "C33" bit came from - makes no sense to me.
Having it or not makes no difference.

That implies that I actually have the syntax correct but something
else is stopping the code from executing. I have the code
"Charts(1).Activate" prior to all of this to ensure it's looking at
the right place. For a little while I had the same problem changing
the title of the chart, but fixed that. I can't for the life of me
remember how... I would suspect a data type mismatch, but that
wouldn't make sense if I could change the data source using the other
bit of code!

The program is horribly complicated and the actual graphing is the
easy bit, or so I thought. I don't, repeat DON'T, want to add a new
series to the chart, because otherwise I'll have a dozen or more lines
running across it. I just want to change the data source for an
EXISTING series. I can do it for a single series using the
Charts(1).SetSourceDate code but don't see a way to access individual
series in the same way. The program is designed to be very dynamic -
graphing multiple interpretations of the dataset within a few
seconds/minutes instead of taking a few days by hand: hence adding new
series with every iteration isn't going to be helpful. When the chart
is created I load it into the actual interface Form as the background
picture of a label and save/print it together with a description of
the filtered dataset and all the filters/modifications I've applied.
The graph really needs to be as tidy as possible.

If it helps, my X values are always the same, for both series. I
really only need to change the Y values.

I guess if it comes to the worst I can set up the ranges manually and
just move numbers in and out of them using the program, but the fact
that I can't do this has begun to bug me ;o) Every other problem I've
had I've managed to solve myself or looking online, but not this one.
Help would be appreciated.

I'm using Win2000, VB 6.3.8863, Excel 2002 SP2. Also WinXP, Excel
2002 SP3, VBA 6.4.8869 for some of the time (i.e. working from home!).

Cheers

Bennett