Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - How to change series name in VBA

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...