Frank -

I'm replying to both of your posts. First a few questions:

1.
I am trying to handle multiple datacollections and thought the array would
be an easier way to do it.<<

Arrays are not necessarily going to be easier than worksheet ranges, and worksheet
ranges will not be prone to some errors what will occur in arrays.

2. How do you determine region? It's not clear from your VBA procedure.

3. Again, you did not add an array using .Extend, but a string; also OLH says if you
use an array, the Rowcol and CategoryLabels arguments will be ignored, so it sounds
like 2D arrays are not welcome (I tried 1 and 2D). If you use a worksheet range for
the data, you could use .Extend with a range. The array technique I showed in my
earlier post does in fact work.

4.
I wanted dis-contiguous data for my x and y values<<

Why?

Advice:

If your data comes from a well defined rectangular range, instead of .Extend, just
redefine the source data range:

ActiveChart.SetSourceData Source:=<range, PlotBy:=xlColumns

It sounds like your data is in a nice list, so you could automate a pivot table to
arrange a plotting data range on an extra sheet. Make real charts from this data,
though, not pivot charts. Real charts are much more flexible.

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

Frank & Pam Hayes wrote:

Jon,

Here is the current VBA Code. The code assumes that you have a Workbook
with a worksheet called Data and a Chart Sheet called Chart1. The Data
worksheet is set up with Region Number (1,2, or 3) in Col A, the Revenue
number in Col C, and the Margin % in Col E. Columns B and D were
intentionally left blank because I wanted dis-contiguous data for my x and y
values. Row 1 is a header row and I put data in rows 2 through 11.

I set up the Chart by creating a scatterplot of the x any y values on the
data sheet and then deleting the series.

The VBA compiles and runs to completion, but it does not produce the result
I was looking for. It does not extend the series it adds. I think I have
something wrong in the syntax of the extend command, but I can not figure it
out. Any advice?


Option Explicit

Sub ExtendChartSeries()

'This code assumes a worksheet called "Data" with the
'Region Number is in Column A
'xValue (Revenue) is in Column C
'yValue (Var Margin) is in Column E
'D and E left blank to force non-contiguous logic

'There is a header row in Row 1
'There is actual data in Rows 2 through 11 for testing purpose only
'It also assumes a pre-existing chart of the Revenue and Margin

' The code to remove duplicates is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' This area defines the reange in which I want to look for unique items
Set AllCells = Worksheets("Data").Range("A2:A11")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0

'Define the SeriesCollection
Dim SeriesCollection As Collection

'Add a ChartSeries for each unique item
Charts("Chart1").Activate
For Each Item In NoDupes
ActiveChart.SeriesCollection.NewSeries
Next Item

'this is test setting only ... will add code to determine actual
Dim TotalRows As Integer
TotalRows = 10

'Extend the datapoints
Dim DataRange As String
Dim DataSeries As Integer
Dim Row As Integer

Charts("Chart1").Activate
Row = 2
For Row = 2 To TotalRows + 1
'Determine the Region Number for the DataSeries
DataSeries = Sheets("Data").Range("A" & Row).Value
ActiveChart.SeriesCollection(DataSeries).Select

'Determine the X and Y Value
DataRange = "C" & Row & ",E" & Row

'Extend the series
ActiveChart.SeriesCollection.Extend
Source:=Sheets("Data").Range(DataRange), _
Rowcol:=xlColumns, CategoryLabels:=True
Next Row

End Sub

Jon,

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.

Frank


"Jon Peltier" wrote in message
...
Frank -

I'm replying to both of your posts. First a few questions:

1.
I am trying to handle multiple datacollections and thought the array
would
be an easier way to do it.<<

Arrays are not necessarily going to be easier than worksheet ranges, and
worksheet ranges will not be prone to some errors what will occur in
arrays.
*** I agree. I have been using arrays in some of my code to speed
calculations and thought that since I already had the data in an array,
perhaps I could write it directly to a chart ***

2. How do you determine region? It's not clear from your VBA procedure.
*** I determine the region using a defined name in the worksheet which
utilizes the Offset command to handle variable number of rows. Sorry I
forgot to account for that in the previous discussion. ***

3. Again, you did not add an array using .Extend, but a string; also OLH
says if you use an array, the Rowcol and CategoryLabels arguments will be
ignored, so it sounds like 2D arrays are not welcome (I tried 1 and 2D).
If you use a worksheet range for the data, you could use .Extend with a
range. The array technique I showed in my earlier post does in fact work.
*** Jon, I took you advice in the previous post and stopped trying to use
the array. I am now trying to use .Extend with a range, but the range is
only a single X and Y value. Since the Sales region can change with every
record, I wanted to Extend one row at a time. I realize I could sort the
data and add a range as in your example, but I wanted to see if I could
avoid the intermediate step and add each row independently ... hence my
question above. ***

4.
I wanted dis-contiguous data for my x and y values<<

Why?
*** The application providing the source data to the users sends the data in
dis-contiguous columns.***

Advice:

If your data comes from a well defined rectangular range, instead of
.Extend, just redefine the source data range:

ActiveChart.SetSourceData Source:=<range, PlotBy:=xlColumns

It sounds like your data is in a nice list, so you could automate a pivot
table to arrange a plotting data range on an extra sheet. Make real charts
from this data, though, not pivot charts. Real charts are much more
flexible.
*** Jon, Thank you for the advice. I agree that I could copy the data
through either a pivot table or to a hidden sheet sorted on the region and
then use the above technique to produce the chart. ***

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

Frank & Pam Hayes wrote:

Jon,

Here is the current VBA Code. The code assumes that you have a Workbook
with a worksheet called Data and a Chart Sheet called Chart1. The Data
worksheet is set up with Region Number (1,2, or 3) in Col A, the Revenue
number in Col C, and the Margin % in Col E. Columns B and D were
intentionally left blank because I wanted dis-contiguous data for my x
and y values. Row 1 is a header row and I put data in rows 2 through 11.

I set up the Chart by creating a scatterplot of the x any y values on
the data sheet and then deleting the series.

The VBA compiles and runs to completion, but it does not produce the
result I was looking for. It does not extend the series it adds. I
think I have something wrong in the syntax of the extend command, but I
can not figure it out. Any advice?


Option Explicit

Sub ExtendChartSeries()

'This code assumes a worksheet called "Data" with the
'Region Number is in Column A
'xValue (Revenue) is in Column C
'yValue (Var Margin) is in Column E
'D and E left blank to force non-contiguous logic

'There is a header row in Row 1
'There is actual data in Rows 2 through 11 for testing purpose only
'It also assumes a pre-existing chart of the Revenue and Margin

' The code to remove duplicates is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' This area defines the reange in which I want to look for unique items
Set AllCells = Worksheets("Data").Range("A2:A11")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0

'Define the SeriesCollection
Dim SeriesCollection As Collection

'Add a ChartSeries for each unique item
Charts("Chart1").Activate
For Each Item In NoDupes
ActiveChart.SeriesCollection.NewSeries
Next Item

'this is test setting only ... will add code to determine actual
Dim TotalRows As Integer
TotalRows = 10

'Extend the datapoints
Dim DataRange As String
Dim DataSeries As Integer
Dim Row As Integer

Charts("Chart1").Activate
Row = 2
For Row = 2 To TotalRows + 1
'Determine the Region Number for the DataSeries
DataSeries = Sheets("Data").Range("A" & Row).Value
ActiveChart.SeriesCollection(DataSeries).Select

'Determine the X and Y Value
DataRange = "C" & Row & ",E" & Row

'Extend the series
ActiveChart.SeriesCollection.Extend
Source:=Sheets("Data").Range(DataRange), _
Rowcol:=xlColumns, CategoryLabels:=True
Next Row

End Sub

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/
_______

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/
_______

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/
_______