使用循环命令绘制散点图

Question

编程主体如下所示：

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条曲线。 想知道是否有办法通过使用循环语句来简化上述脚本，因为所有系列都有一个要重复的公式。

Answer 1

未经测试：

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

Answer 2

试试吧。

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
End with
End Sub