Bubble Chart

[Austin]

Hi, I am trying to create a bubble chart that (I think its required that
each series be independent) has a variable number of series. For example, as
a start I have the code below. This creates a proper bubble chart.

I want to be able to select an array (variable length) then run the macro
and it will do something similar to the actions below for each series in the
array.

I am comfortable in VBA so there isnt any need to explain the simple stuff
but this is a little beyond my reach. Can you use a for...next to run
through and add each of the series?

Thanks a lot for any help


Sub Macro1()

'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlBubble3DEffect
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Sheet1'!$A$2"
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$D$2"
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$C$2"
ActiveChart.SeriesCollection(1).BubbleSizes = "='Sheet1'!$B$2"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Sheet1'!$A$3"
ActiveChart.SeriesCollection(2).XValues = "='Sheet1'!$D$3"
ActiveChart.SeriesCollection(2).Values = "='Sheet1'!$C$3"
ActiveChart.SeriesCollection(2).BubbleSizes = "='Sheet1'!$B$3"
End Sub

 

[Jon Peltier]

This ancient post has a macro that does pretty much what you want:

http://groups.google.com/group/micr...2dfcc9/98807e1c12c9afd2?#doc_83e922c391279403

Why do you need one point per bubble series? For the labels? Use Rob
Bovey's Chart Labeler, a free Excel add-in from http://appspro.com, to
apply labels from a worksheet range to your data points.

- Jon