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