Resize points in scatterplot depending on values

[Schadenfreud]

I have this chart and I'm trying to resize its points depending on the 'Size' values [D2:D8]. I found some code that should resize the points depending on some values, but it gives me 'Run time error 91 object variable or with block not set'.

Here's the said code:

Sub ChangeScatterplotSizeMarker()


Dim rngSizes As Range
Dim lngIndex As Long


' range containing marker size
Set rngSizes = Range("D2:D8")


With ActiveChart
    With .SeriesCollection(1) <-- error 91 here
        For lngIndex = 1 To .Points.Count
            With .Points(lngIndex)
                 .MarkerSize = rngSizes.Cells(lngIndex).Value
                 .MarkerBackgroundColorIndex = _
                 rngSizes.Cells(lngIndex).Interior.ColorIndex
                 .MarkerForegroundColorIndex = _
                 rngSizes.Cells(lngIndex).Interior.ColorIndex
            End With
        Next
    End With
End With


End Sub

I'm using that code in Book1.xlsx - Sheet1 (Code), so I'm assuming it should be able to find the chart... but it might not be the case. Suggestions, solutions, help please?

 

[pgc01]

Hi

Shapes are not in cells, they can just happen to be be located over a cell.

You should give meaningful name to the shapes and define criteria that allows you to choose which one you should choose.

For this example I assumed:

- I have my chart as before in Sheet1
- I have 3 shapes in Sheet2 that I want to apply as markers, named "Pear", "Apple", "Orange"
- for each point in the chart I will choose "Pear" for a value less or equal to 5, "Apple" for a value between 5 and 10 and "Orange" otherwise

To test the code
- have a scatter chart in sheet 1 with some points with Y values between 1-15.
- change the names of 3 shapes in sheet2 to the names I wrote (or amend the code) and resize them so that they can be applied as markers in the chart.

Run the code (same logic as before, just using other shapes)

Sub AddCustomMarkers()
Dim wsCht As Worksheet, wsShp As Worksheet
Dim cht As Chart
Dim shp As Shape
Dim j As Long
Dim vNames As Variant, vValues As Variant

' names of the shapes and worksheet where they are located
vNames = Array("Pear", "Apple", "Orange")
Set wsShp = Worksheets("Sheet2")

' worksheet where the chart is located and chart object
Set wsCht = Worksheets("Sheet1")
Set cht = wsCht.ChartObjects(1).Chart

With cht.SeriesCollection(1)
    vValues = .Values
    For j = 1 To .Points.Count
        ' select which shape to use
        Select Case vValues(j)
            Case Is <= 5: Set shp = wsShp.Shapes("Pear")
            Case Is <= 10: Set shp = wsShp.Shapes("Apple")
            Case Else: Set shp = wsShp.Shapes("Orange")
        End Select
        
        ' apply as marker
        shp.Copy
        .Points(j).Paste
    Next j
End With

End Sub

Remark:
I didn't care about the size of the markers, they are all applied with the same size for this example.
After you run the test you can resize them using the code I posted before, where I would set the height and weight for the shape.