Bubble graph help

[Ghost World]

Hello,

I'm sure this is probably a very simple issue that I'm overlooking, but here goes:

I want to create a bubble chart which plots employees by their rating against two criteria ("Tasks" and "Behaviours", both on a scale of 0-10) with bubble size determined by job role.

I have a worksheet with the following info:

M18:M50 has the series' name
N18:N50 has the X-axis value ("Tasks")
O18:O50 has the Y-axis value ("Behaviours")
P18:P50 has the bubble size value (job role as a numeric value, 1-5)

When I try to create a Bubble Chart from that data, the Chart Wizard

When I correct this to

Series 1
Name: $M$18
X Values: $N$18
Y Values: $O$18
Sizes: $P$10

It displays exactly as I want it, but I have a lot of series and so can't do this manually for each. How can I get Excel to automatically recognise N18 as the X value, O18 as the Y value etc.?

I hope that makes sense, let me know if it doesn't. Thanks in advance for any help.

[Andy Pope]

Getting multi series bubbles is a pain.

try this coded approach.

Sub MakeBubble()

    Dim chtTemp As Chart
    Dim rngData As Range
    Dim lngRow As Long
    
    Set rngData = Range("M18:P50")
    Charts.Add
    ActiveChart.Location xlLocationAsObject, rngData.Parent.Name
    Set chtTemp = rngData.Parent.ChartObjects(rngData.Parent.ChartObjects.Count).Chart
    
    Do While chtTemp.SeriesCollection.Count > 0
        chtTemp.SeriesCollection(1).Delete
    Loop
    
    chtTemp.ChartType = xlXYScatter
    For lngRow = 1 To rngData.Rows.Count
        With chtTemp.SeriesCollection.NewSeries
            .Name = rngData.Cells(lngRow, 1)
            .XValues = rngData.Cells(lngRow, 2)
            .Values = rngData.Cells(lngRow, 3)
        End With
    Next
    
    chtTemp.ChartType = xlBubble
    For lngRow = 1 To rngData.Rows.Count
        If lngRow > chtTemp.SeriesCollection.Count Then
            chtTemp.SeriesCollection.NewSeries
        End If
        With chtTemp.SeriesCollection(lngRow)
            .Name = rngData.Cells(lngRow, 1)
            .XValues = rngData.Cells(lngRow, 2)
            .Values = rngData.Cells(lngRow, 3)
            .BubbleSizes = "='" & rngData.Parent.Name & "'!" & rngData.Cells(lngRow, 4).Address(, , xlR1C1)
        End With
    Next
    
End Sub