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