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

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

http://groups.google.com/group/micro...e922c391279403

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
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Austin wrote:
> 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