How to change series name in VBA

Question

I have a series of charts I am creating using VBA (code below).

I am having trouble changing the names of the series from series 1 and series 2 to Current State and Solution State.

I keep getting an

Object Variable or With Block Variable not set

error.

However without the srs1 and srs2 code the charts work just fine (just with the wrong series names).

I looked up how to fix this and the answer I received however is not working 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

Answer 1

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 inside your With block so you should be able to access it's .SeriesCollection(x).Name properties in the same fashion as you have done for the other properties.

Answer 2

I believe the problem is with referencing - in the code you reference ActiveChart (I am guessing it does not exist), while you have created MAChart in the code above.

 Set Srs1 = MAChart.SeriesCollection(1) 
 Srs1.Name = "Current State"
 Set Srs2 = MAChart.SeriesCollection(2) 
 Srs2.Name = "Proposed Solution"