使用循环命令绘制散点图

收藏

程序的主体如下图所示:

ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "='7G'!$A$2"
ActiveChart.FullSeriesCollection(1).XValues = "='7G'!$B$2:$B$67"
ActiveChart.FullSeriesCollection(1).Values = "='7G'!$N$2:$N$67"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = "='7G'!$A$68"
ActiveChart.FullSeriesCollection(2).XValues = "='7G'!$B$68:$B$133"
ActiveChart.FullSeriesCollection(2).Values = "='7G'!$N$68:$N$133"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(3).Name = "='7G'!$A$134"
ActiveChart.FullSeriesCollection(3).XValues = "='7G'!$B$134:$B199"
ActiveChart.FullSeriesCollection(3).Values = "='7G'!$N$134:$N$199"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(4).Name = "='7G'!$A$200"
ActiveChart.FullSeriesCollection(4).XValues = "='7G'!$B$200:$B$265"
ActiveChart.FullSeriesCollection(4).Values = "='7G'!$N$200:$N$265"

主要是需要在Excel中使用上述脚本绘制50条曲线。想知道是否有一种方法可以通过使用循环语句来简化上述脚本,因为所有系列都有重复的公式。

回复
  • 尝试一下。

    Sub test()
        Dim Cht As Chart
        Dim Shs As Series
        Dim Ws As Worksheet
        Dim rngName As Range
        Dim i As Integer, n As Integer
    
        Set Cht = ActiveChart
        Set Ws = Sheets("7G")
        n = 2
        With Cht
            For i = 1 To 50
                Set rngName = Ws.Range("a" & n)
                Set Shs = .SeriesCollection.NewSeries
                With Shs
                    .Name = rngName
                    .XValues = rngName.Offset(, 1).Resize(66)
                    .Values = rngName.Offset(, 3).Resize(66)
                End With
                n = n + 66
            Next i
        Next Cht
    
    End Sub
    

  • 未经测试:

    Dim i, rowOff As Long
    Dim cht As Chart
    
    Set cht = ActiveChart
    
    For i = 1 To 50
        rowOff = (i - 1) * 66 'calculate offset for this set
        With cht.SeriesCollection.NewSeries
            .Name = Sheets("7G").Range("a2").Offset(rowOff, 0).Value
            .XValues = Sheets("7G").Range("B2:B67").Offset(rowOff, 0).Value
            .Values = Sheets("7G").Range("N2:N67").Offset(rowOff, 0).Value
        End With
    Next i