VBA Excel - changing series source.
Jon Peltier wrote in message ...
Nick -
SetSourceData tells the chart what its entire range should be. This is fine if the ranges are
all neatly aligned, and all series use the same range for their X values.
Thanks for that: it's what I suspected, hence the need for some kind
of series-specific code!
What you need is to set the X and Y ranges for each series separately:
With Chart(1).SeriesCollection(1)
.Values = Worksheets("DataSet").Range("AI3:AI28")
.XValues = Worksheets("DataSet").Range("AH3:AH28")
.Name = Worksheets("DataSet").Range("AI2")
End With
With a little tweaking I think I've got it to work: minor hiccup
though, it should read
With ChartS(1)...
which had me head-scratching for a bit ;-)
I was under the impression that the With commands were only for
setting up a chart at the start of a program or something, but it
seems reasonably happy with changing it on the fly.
Note for any readers with the same/similar problems that I expect it
only works if you've already established a series the the sort of
errors I was getting imply that unless you've previously established a
NewSeries with a range etc you can't then go in and change it - since
my chart and series pre-exists that criteria is filled. This is
probably obvious to anyone with much experience with VBA but I'm
learning as I go!
I'm pretty sure I should be okay from here on. Thanks very much for
the assistance.
Cheers
Bennett
|