Do you need to do it in VBA? You could make a pivot table in Excel which
will produce a table with Days 1, 2, 3 down the first column and Missions A,
B, and C across the top row. This is ideally suited to creating a chart,
either a pivot chart or a regular chart:
http://pubs.logicalexpressions.com/P...cle.asp?ID=553
If you do want to use VBA, don't be afraid of looping. To clear out unwanted
series:
Sub RemoveUnwantedSeries()
With ActiveChart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With
End Sub
(excerpted from
http://peltiertech.com/Excel/ChartsH...kChartVBA.html,
which has a lot of other hints too).
To find the series, you start at the first row of mission data (row i1),
read what the mission is, and then read each row after that until you get to
a different value. This is row i2, so your series is defined as going from
i1 to i2-1. Build a string in R1C1 notation and use the code you posted.
Then set the new value of i1 equal to i2, and continue down your data.
While the looping may seem tedious, it's pretty fast. On a recent project I
used this system to create about 30 charts from a 1200 row worksheet.
Including adding a worksheet for each chart, putting some labels on the
sheet, creating the chart, and formatting it, the program build 30 charts in
under 5 seconds.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Dale Fye" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I've been working on this for a couple of days now, so bear with me. I've
> got some data (linked from an Access query) that looks like:
>
> Mission Day Sorties
> A 1 9
> A 2 7
> A 3 5
> B 1 10
> B 2 11
> B 3 19
> C 1 5
> C 2 16
> C 3 12
>
> The types of missions vary, as do the number of day (although for a
> particular query, the number of day will be constant across each of the
> missions). What I want to do is dynamically format the data series of a
> stacked bar chart so that each of the Missions is a different data series,
> the days are the X-Axis Values, and the Sorties reflect the height of the
> bars in the stacked bar chart.
>
> I'm relatively new to Excel VBA, but have been using Access for about 10
> years. I'm just not familiar with the Excel object model. The first
> problem I am having is actually selecting the chart so that I actually
> have an "ActiveChart" object. Can anyone help me out with that step?
>
> Once I have selected a chart, I want to clear out it's SeriesCollection;
> figured out a way to do that one series at a time but would be interested
> in knowing if that can be done in a single step.
>
> Next, I assume I am going to have to do some looping to identify the start
> and end point of each series (mission), but should probably start out by
> clearing out the Series collections. Is there an easy way within Excel to
> identify the row that contains the next value. For example, Mission type
> "A" starts on Row 2, and mission type "B" starts on row 5. Is there a
> quick way to identify Row 5 without looping through each row and testing
> the value of the first cell in each row against some preset value? In
> most cases, my dataset will contain around a thousand rows (5-10 mission
> types)
>
> I assume that once I define the start and end positions of each series,
> I'll be able to use code similiar to the following to build the series.
>
> ActiveChart.SeriesCollection.NewSeries
> ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C2:R4C2"
> ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C3:R4C3"
> ActiveChart.SeriesCollection(1).Name = "=Sheet1!R2C1"
>
> Any help would be greatly appreciated
>
Similar Threads
