Adding values to a bubble chart

[GiorgioCTS]

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

 

[Peter T]

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]

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.

 

[Andy Pope]

Try,

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

Cheers
Andy

 

[GiorgioCTS]

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