How to change series name in VBA
I have a series of charts that I am creating with VBA (code below).
I am having trouble changing the series titles from Series 1 and Series 2 to the current state and solution state.
I keep getting
Object variable or with block variable not set
mistake.
However, without code srs1
and srs2
diagrams work just fine (only with wrong series names).
I looked at how to fix this and the answer I received however does not work for me.
Does anyone know another way to do this?
Sub MA()
Dim Srs1 As Series
Dim Srs2 As Series
Dim i As Integer
Dim MAChart As Chart
Dim f As Integer
f = 2 * Cells(2, 14)
For i = 1 To f Step 2
Set MAChart = ActiveSheet.Shapes.AddChart(Left:=750, Width:=400, Top:=130 + 50 * (i - 1), Height:=100).Chart
With MAChart
.PlotBy = xlRows
.ChartType = xlColumnClustered
.SetSourceData Source:=ActiveSheet.Range("Q" & 1 + i & ":Z" & 2 + i)
.Axes(xlValue).MaximumScale = 4
.Axes(xlValue).MinimumScale = 0
.HasTitle = True
.ChartTitle.Text = "Provider Load for " & Cells(i + 1, 15)
'where errors start- works fine up to this point
Set Srs1 = ActiveChart.SeriesCollection(1)
Srs1.Name = "Current State"
Set Srs2 = ActiveChart.SeriesCollection(2)
Srs2.Name = "Proposed Solution"
End With
Next i
End Sub
source to share
Try changing these lines ...
Set Srs1 = ActiveChart.SeriesCollection(1)
Srs1.Name = "Current State"
Set Srs2 = ActiveChart.SeriesCollection(2)
Srs2.Name = "Proposed Solution"
To ...
.SeriesCollection(1).Name = "Current State"
.SeriesCollection(2).Name = "Proposed Solution"
You are already using MAChart
a With block in your block so that you can access these properties .SeriesCollection(x).Name
in the same way as you would for other properties.
source to share
I believe the issue is with the link - in the code you link to ActiveChart (I assume it doesn't exist), while you created the MAChart in the code above.
Set Srs1 = MAChart.SeriesCollection(1)
Srs1.Name = "Current State"
Set Srs2 = MAChart.SeriesCollection(2)
Srs2.Name = "Proposed Solution"
source to share