Adding values to a bubble chart

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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" <[email protected]> wrote in message
> >>news:[email protected]...
> >>
> >>>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
>