I need to automate the process of adding one bubble to a bubble chart. Most
of the code works fine, however, I cannot define the size of the bubble
correctly as it seems that I must use R1-style notation. Any assistance would
be greatly appreciated.

Sub AddBubble()
Dim rng As Variant
Dim rng2 As Variant
Dim rng3 As Variant
Dim rng4 As Variant

Set rng = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng2 = ActiveCell
ActiveCell.Offset(0, 2).Range("A1").Select
Set rng3 = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng4 = ActiveCell

ActiveSheet.ChartObjects("BubbleChart").Activate
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = rng2
ActiveChart.SeriesCollection(2).Values = rng3
ActiveChart.SeriesCollection(2).Name = rng
ActiveChart.SeriesCollection(2).BubbleSizes = rng4
ActiveChart.ChartType = xlBubble
End Sub

Assuming you are putting valid data in your range variables, try changing
the second part of your routine as follows -

With ActiveSheet.ChartObjects("BubbleChart").Chart
.SeriesCollection.NewSeries
With .SeriesCollection(.SeriesCollection.Count)
.XValues = rng2
.Values = rng3
.Name = rng
.BubbleSizes = rng4
End With
End With

Regards,
Peter T


"GiorgioCTS" wrote in message
...
I need to automate the process of adding one bubble to a bubble chart.
Most
of the code works fine, however, I cannot define the size of the bubble
correctly as it seems that I must use R1-style notation. Any assistance
would
be greatly appreciated.

Sub AddBubble()
Dim rng As Variant
Dim rng2 As Variant
Dim rng3 As Variant
Dim rng4 As Variant

Set rng = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng2 = ActiveCell
ActiveCell.Offset(0, 2).Range("A1").Select
Set rng3 = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng4 = ActiveCell

ActiveSheet.ChartObjects("BubbleChart").Activate
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = rng2
ActiveChart.SeriesCollection(2).Values = rng3
ActiveChart.SeriesCollection(2).Name = rng
ActiveChart.SeriesCollection(2).BubbleSizes = rng4
ActiveChart.ChartType = xlBubble
End Sub

I still get a debug error on the line ".BubbleSizes = rng4". The bubble sizes
are in percentages, which when I add the data manually, works. I have also
tried changing the data to comma format numbers and still the code stops at
this line.



"Peter T" wrote:

Assuming you are putting valid data in your range variables, try changing
the second part of your routine as follows -

With ActiveSheet.ChartObjects("BubbleChart").Chart
.SeriesCollection.NewSeries
With .SeriesCollection(.SeriesCollection.Count)
.XValues = rng2
.Values = rng3
.Name = rng
.BubbleSizes = rng4
End With
End With

Regards,
Peter T


"GiorgioCTS" wrote in message
...
I need to automate the process of adding one bubble to a bubble chart.
Most
of the code works fine, however, I cannot define the size of the bubble
correctly as it seems that I must use R1-style notation. Any assistance
would
be greatly appreciated.

Sub AddBubble()
Dim rng As Variant
Dim rng2 As Variant
Dim rng3 As Variant
Dim rng4 As Variant

Set rng = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng2 = ActiveCell
ActiveCell.Offset(0, 2).Range("A1").Select
Set rng3 = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng4 = ActiveCell

ActiveSheet.ChartObjects("BubbleChart").Activate
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = rng2
ActiveChart.SeriesCollection(2).Values = rng3
ActiveChart.SeriesCollection(2).Name = rng
ActiveChart.SeriesCollection(2).BubbleSizes = rng4
ActiveChart.ChartType = xlBubble
End Sub

Try,

..BubbleSizes = "='" & rng4.Parent.Name & "'!" _
& rng4.Address(ReferenceStyle:=xlR1C1)

Cheers
Andy

GiorgioCTS wrote:
I still get a debug error on the line ".BubbleSizes = rng4". The bubble sizes
are in percentages, which when I add the data manually, works. I have also
tried changing the data to comma format numbers and still the code stops at
this line.



"Peter T" wrote:


Assuming you are putting valid data in your range variables, try changing
the second part of your routine as follows -

With ActiveSheet.ChartObjects("BubbleChart").Chart
.SeriesCollection.NewSeries
With .SeriesCollection(.SeriesCollection.Count)
.XValues = rng2
.Values = rng3
.Name = rng
.BubbleSizes = rng4
End With
End With

Regards,
Peter T


"GiorgioCTS" wrote in message
...

I need to automate the process of adding one bubble to a bubble chart.

Most

of the code works fine, however, I cannot define the size of the bubble
correctly as it seems that I must use R1-style notation. Any assistance

would

be greatly appreciated.

Sub AddBubble()
Dim rng As Variant
Dim rng2 As Variant
Dim rng3 As Variant
Dim rng4 As Variant

Set rng = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng2 = ActiveCell
ActiveCell.Offset(0, 2).Range("A1").Select
Set rng3 = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng4 = ActiveCell

ActiveSheet.ChartObjects("BubbleChart").Activate
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = rng2
ActiveChart.SeriesCollection(2).Values = rng3
ActiveChart.SeriesCollection(2).Name = rng
ActiveChart.SeriesCollection(2).BubbleSizes = rng4
ActiveChart.ChartType = xlBubble
End Sub




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Andy, I raise my glass to you as this worked! I assume this code takes a
range and converts it to R1-style notation? Thanks to you (and Peter) and
have a blessed day. Giorgio

"Andy Pope" wrote:

Try,

..BubbleSizes = "='" & rng4.Parent.Name & "'!" _
& rng4.Address(ReferenceStyle:=xlR1C1)

Cheers
Andy

GiorgioCTS wrote:
I still get a debug error on the line ".BubbleSizes = rng4". The bubble sizes
are in percentages, which when I add the data manually, works. I have also
tried changing the data to comma format numbers and still the code stops at
this line.



"Peter T" wrote:


Assuming you are putting valid data in your range variables, try changing
the second part of your routine as follows -

With ActiveSheet.ChartObjects("BubbleChart").Chart
.SeriesCollection.NewSeries
With .SeriesCollection(.SeriesCollection.Count)
.XValues = rng2
.Values = rng3
.Name = rng
.BubbleSizes = rng4
End With
End With

Regards,
Peter T


"GiorgioCTS" wrote in message
...

I need to automate the process of adding one bubble to a bubble chart.

Most

of the code works fine, however, I cannot define the size of the bubble
correctly as it seems that I must use R1-style notation. Any assistance

would

be greatly appreciated.

Sub AddBubble()
Dim rng As Variant
Dim rng2 As Variant
Dim rng3 As Variant
Dim rng4 As Variant

Set rng = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng2 = ActiveCell
ActiveCell.Offset(0, 2).Range("A1").Select
Set rng3 = ActiveCell
ActiveCell.Offset(0, 1).Range("A1").Select
Set rng4 = ActiveCell

ActiveSheet.ChartObjects("BubbleChart").Activate
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = rng2
ActiveChart.SeriesCollection(2).Values = rng3
ActiveChart.SeriesCollection(2).Name = rng
ActiveChart.SeriesCollection(2).BubbleSizes = rng4
ActiveChart.ChartType = xlBubble
End Sub




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info