excel 为什么我在使用此图表宏更新时收到此参数无效错误

pkwftd7m  于 2023-05-08  发布在  其他
关注(0)|答案(1)|浏览(114)
Sub Graph_YC()
' update graph in monthly Yield Curve file using RowIndex in Col.P
Dim i As Integer
Dim RowIndex As Integer

    ActiveSheet.ChartObjects("Chart 1").Activate
    For i = 1 To 6
    RowIndex = ActiveSheet.Range("P8").Offset(i - 1, 0).Value
        With ActiveChart.SeriesCollection(i)
            .Name = ActiveSheet.Cells(RowIndex, 13)
            .Values = ActiveSheet.Range(Cells(RowIndex, 2), Cells(RowIndex, 11))
            .XValues = ActiveSheet.Range("$B$3:$K$3")
        End With
    Next
    
    Rows("16:25").EntireRow.Hidden = True
End Sub

这是一个没有VBA知识的问题,被告知要解决它。我的猜测是,这与图表对象的初始化有关,但我不确定。任何帮助将不胜感激。
代码错误出现在写着WithActiveChart.SeriesCollection(i)的行。错误是无效的参数,所以我猜它与图表的初始化有关。

nnsrf1az

nnsrf1az1#

这将是一个更强大一点。最好尽量避免使用Activate

Sub Graph_YC()
    Dim i As Long, RowIndex As Long, cht As Chart, ws As Worksheet

    Set ws = ActiveSheet
    Set cht = ws.ChartObjects("Chart 1").Chart
    
    For i = 1 To 6
        RowIndex = ws.Range("P8").Offset(i - 1, 0).Value
        With cht.SeriesCollection(i)
            .Name = ws.Cells(RowIndex, 13)
            .Values = ws.Cells(RowIndex, 2).Resize(1, 10)
            .XValues = ws.Range("$B$3:$K$3")
        End With
    Next
    
    ws.Rows("16:25").EntireRow.Hidden = True
End Sub

相关问题