Small problems with generating a Bubble Chart in VBA

[BobBarker]

Hi.

I am trying to create a bubble chart in purely VBA. The reason why I'm doing this is because I want to put text labels on each bubble and Excel only allows me to put the series name, which ends up being everything. So rather than adding in series row by row, I've decided to make a script that does it for me.

I've managed to get my code to run, sorta. The problems I'm having are that it is skipping rows for some reason and also I don't know how to specify the size column.

I've attached my code below. Any help is appreciated. I got really close this time!

[EDIT] I now realize that it is fetching all the rows properly! The problem is that I'm not specifying the size column. Still don't know how :/

[BobBarker]

Well, here's my final macro:
Sub AddChartObject()
' Declaring some variable for later
Dim lastRow As Long
Dim oldCount As Long

' Create the chart
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225) ' Sets the position and size of the chart
' Sets some arbitrary source cells, we will delete these after
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("B5:M20")
' We want a bubble chart
.Chart.ChartType = xlBubble

' This counts and stores into a variable the amount of random series the chart made when it was created
' These series will be deleted at the end, but we need them for the chart to be generated
oldCount = .Chart.SeriesCollection.Count
' How many random series there are, in a message box to us
MsgBox (oldCount)


' Gets the last row number and stores it into a variable
lastRow = ActiveSheet.Cells.Find("*", ActiveSheet.Cells(1, 1), , , 1, 2).Row

' Add the series in starting at row #4, until the lastRow, jump by 1 row at a time
For i = 4 To lastRow Step 1
With .Chart.SeriesCollection.NewSeries
'Get the name from Cell B4+how many iterations we're on
.Name = ActiveSheet.Range("B" & i)
'Get the Y value from Cell F4+how many iterations we're on
.Values = ActiveSheet.Range("F" & i)
'Get the X value from Cell I4+how many iterations we're on
.XValues = ActiveSheet.Range("I" & i)
' Get the sizes - even I don't understand how this works, but it does it properly from Cell Mi+iteration
sizestr = ActiveSheet.Range("M" & i).Address(ReferenceStyle:=xlR1C1, external:=True)
.BubbleSizes = "=" & sizestr
End With
'continue the loop until the last row
Next


'This deletes the random series we had when we generated the chart
Do Until oldCount = 0
.Chart.SeriesCollection(1).Delete
oldCount = oldCount - 1
Loop

'The end.
End With
End Sub

For some reason, on some sheets I get "Subscript is out of range" errors.
IF anyone knows why that happens, I'd be very grateful. Unfortunately I cannot attach a sheet where that occurs.