Excel VBA：为什么没有添加任何后续系列？

我试图将大约 200 个系列绘制到一个图表上，并尝试使用 for 循环为我绘制所有系列，但是，当我运行以下代码时，只有第一个系列（不在 for 循环中）实际上进入了图表。 for 循环生成的所有其他内容都没有显示，我不知道为什么......

Sub Macro3()

    Dim r As Integer
    Dim cellName As String
    Dim first As String
    Dim second As String
    Dim newCell As String
    Dim xAxis As String
    Dim i As Integer

    xAxis = "=Compilation!A7:A7507"
    originCell = "=Compilation!B7:B7507"

    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = Range(xAxis)
    ActiveChart.SeriesCollection(1).Values = Range(originCell)

    For i = 2 To 200
        ' Incriment the columns
        first = InStr(originCell, "!")
        second = InStr(first + 1, originCell, ":")

        cellName = Mid(originCell, first + 1, second - first - 1)
        r = Range(cellName).Column + 1

        newCell = Replace(originCell, cellName, cellS(7, r).Address(RowAbsolute:=False, ColumnAbsolute:=False))

        If (cellS(7, r - 1).Value = "YES") Then
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(i).XValues = Range(xAxis)
            ActiveChart.SeriesCollection(i).Values = Range(newCell)
        End If

        originCell = newCell
        MsgBox (i & ", " & xAxis & ", " & newCell)
    Next i


End Sub

任何见解将不胜感激！提前致谢！

从您的代码中，我相信您想要绘制垂直而不是水平的数据系列，并且具有非常误导性的变量名称 - 使用r for Columns.

首先，去掉等号Range() to work

xAxis = "Compilation!A7:A7507"
originCell = "Compilation!B7:B7507"

然后，您不断向图表中添加系列，无论其中有多少。

下一个问题是ActiveChart.SeriesCollection(i)不应该涉及到i，因为您只想添加“是”。

假设第 7 行中的单元格可能等于“YES”，下面的代码应该适合您。如果为“是”，则其下方的数据将添加到图表中（不应像您所做的那样包含其自身）。如果 Activesheet 中没有图表，也会处理。在添加“是”系列之前，它将删除图表中的所有旧系列。

评论出我的测试数据行并取消注释实际数据:

Sub AddDataToChart1()
    Const YesNoRow As Long = 7 ' Yes/No should not be plotted in the chart
    Const xAxis As String = "Compilation!A8:A13" ' TEST DATA
    'Const xAxis As String = "Compilation!A8:A7507" ' ACTUAL DATA

    Dim oRngAxis As Range, oCht As Chart
    Dim i As Long ' Offset counter
    Dim n As Long ' Number of data series in chart

    On Error Resume Next
    ' Check if existing chart available
    Set oCht = ActiveSheet.ChartObjects(1).Chart
    If oCht Is Nothing Then Set oCht = ActiveSheet.Shapes.AddChart.Chart
    On Error GoTo 0
    ' Chart Object valid, add series
    If Not oCht Is Nothing Then
        Set oRngAxis = Range(xAxis)
        With oCht
            ' Remove previous data
            For i = .SeriesCollection.Count To 1 Step -1
                .SeriesCollection(i).Delete
            Next
            n = 0
            For i = 1 To 200
                If UCase(oRngAxis.Worksheet.Cells(YesNoRow, oRngAxis.Column + i).Value) = "YES" Then
                    n = n + 1
                    If n > .SeriesCollection.Count Then
                        .SeriesCollection.NewSeries
                    End If
                    .SeriesCollection(n).XValues = oRngAxis
                    .SeriesCollection(n).Values = oRngAxis.Offset(0, i)
                    .SeriesCollection(n).Name = "Col " & Split(oRngAxis.Offset(0, i).Address, "$")(1)
                End If
            Next i
        End With
        Set oCht = Nothing
        Set oRngAxis = Nothing
    End If
End Sub

样本数据和输出：

UPDATE:

将以下代码添加到编译表中，以便每当第 7 行中的单元格发生更改时，它都会立即更新图表！您也可以将子 AddDataToChart1 移动到那里：

Private Sub Worksheet_Change(ByVal oRng As Range)
    If Not Intersect(oRng, Rows(7)) Is Nothing Then AddDataToChart1
End Sub