View Single Post
  #5   Report Post  
Jon Peltier
 
Posts: n/a
Default

Frank -

I try to avoid working with all my series together. Unless it's a simple chart, I
don't use SetSourceData, and I've never used Extend in battle, only for this thread.
I adjust each series as a separate entity, and control the name, values, and xvalues
of each independently of the others. It seems like more work, but it really isn't,
and you gain more control over your chart.

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

Frank & Pam Hayes wrote:

Jon,

I appreciate your help with this. I have discovered the following:

1. When you have a chart with a single existing series that was created
using a Range of data, even it the range is a single xy value, and even if
the x and y values are noncontiguous, the code you wrote below will
successfully extend the dataseries.

2. When there are multiple dataseries in the chart, the extend command will
extend the range of ALL Range series in the collection. I thought that
selecting a specific SeriesCollection number (e.g. SeriesCollection(2))
prior to the extend command would limit the extension to the single series
selected, but I could not get this to work.

3. If the dataseries was created by putting specific values in the chart
(typing the specific x and y values into the Chart - Source Data - Add
dialog box rather than referring to the range on the associated sheet) this
dataseries is treated as an Array and the extend command will not work with
it.

4. If you have added a new series via VBA but have not populated it yet,
Excel will not let you extend the series. I assume it is in there as an
Array and therefore #3 above applies. This was the problem with my original
code posted in the thread.

Jon ... Any advice on #2 above?

Frank



"Jon Peltier" wrote in message
...

Frank -


I have imbedded my answers to your questions below. I do have one
additional question: Can you get the .Extend method to work with a
dis-contiguous range that has a single x and y value (versus a range with
multiple values)? I can not and I do not see what I am doing
incorrectly.


Tested and worked as expected, whether the added range is contiguous with
the existing source data or not:

Sub Macro()
ActiveChart.SeriesCollection.Extend _
Worksheets("Sheet1").Range("B11,D11"), xlColumns, True
End Sub

Categories for the rest of the chart were in column B and values in column
D.

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