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]

I'm using that code in Book1.xlsx - Sheet1 (Code), so I'm assuming it should be able to find the chart...

??

If the chart is not selected you have to reference the chart.

What's the name of the chart?
... or, if you don't know the name of the chart (?), how many are there in worksheet Sheet1, do you know its index?
(name is the obvious choice)

 

[pgc01]

Another question, do you need a special marker? A bubble chart is exactly what you need, a scatter chart with specified marker sizes.

 

[Schadenfreud]

Sub SizeMarker()


Dim rngSizes As Range
Dim lngIndex As Long


' range containing marker size
Set rngSizes = Sheet5.Range("G41:G47")


With Sheet5.ChartObjects(1).Chart
    For lngIndex = 1 To .SeriesCollection.Count
        With .SeriesCollection(lngIndex).Points(1)
             .MarkerSize = rngSizes.Cells(lngIndex).Value
        End With
    Next
End With


End Sub

This is what I needed, it turned out that the points are in separate 'SeriesCollections', the regular points re-size properly, but the custom ones don't for some reason.

 

[pgc01]

I'm glad your problem is solved. Cheers.

 

[Schadenfreud]

I'm glad your problem is solved. Cheers.

Not exactly solved, as I said the custom points still don't re-size. It's been solved only for the excel default points. Any idea why it won't work for the custom ones?

 

[pgc01]

I may be wrong but I don't think you can resize them. The way I've done is to resize the source shape before applying it to the point.

 

[Schadenfreud]

Can you give me some sample code I can work with?

 

[pgc01]

This is an example that replaces the markers with stars with growing sizes

Add 1 scatter chart to Sheet1 with 1 series and execute:

Sub AddCustomMarkers()
Dim ws As Worksheet
Dim cht As Chart
Dim shp As Shape
Dim j As Long

Set ws = Worksheets("Sheet1")
Set cht = ws.ChartObjects(1).Chart
Set shp = ws.Shapes.AddShape(msoShape6pointStar, 100, 100, 5, 5)
shp.Fill.ForeColor.RGB = vbYellow
shp.Line.ForeColor.RGB = vbGreen

With cht.SeriesCollection(1)
    For j = 1 To .Points.Count
        shp.Width = shp.Width + 2
        shp.Height = shp.Height + 2
        shp.Copy
        .Points(j).Paste
    Next j
End With

End Sub

Remark: I assumed there are no other charts in Sheet 1, else amend the index.

 

[Schadenfreud]

You're using an existing shape, I need to add external images. Right now I've added all 7 images in sheet 2 range A1:A7 (I'll choose which image to use according to some criteria), but how can I use the image in Sheet2.Cell[A1] (for example), instead of the msoShape you provided?