我正在尝试动态地向折线图中添加多个系列。我事先不知道有多少个系列,因此需要动态处理。我想到的但不起作用的方法如下:
工作表ActiveSheet(或Sheets("Data"))从C14到Cend包含XValues和从E14:Eend到R14:Rend的列,其中“ end”表示由列C确定的最后一行数据。系列名称存储在第9行。所有系列的XValues相同。
我的主要问题是,我找不到一种方法将所有数据列动态添加到我的图表中,并带有相应的名称。我不是VBA专家,请多多关照。我已经阅读了各种来源并尝试了许多脚本,但似乎没有一个正常工作。对象目录有点帮助,但是我的问题仍然存在。
Sub MakeChart()
Dim LastColumn As Long
Dim LastRow As Long
Dim i As Integer
Dim u As Integer
Dim NameRng As String
Dim CountsRng As Range
Dim xRng As Range
LastColumn = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column
ColumnCount = LastColumn - 4
LastRow = ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
' Debug.Print ("Last Column: " & LastColumn & " Count: " & ColumnCount & " LastRow: " & LastRow)
Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.HasTitle = True
.ChartTitle.Text = "Test"
End With
For i = 1 To ColumnCount
u = i + 4
NameRng = Sheets("Data").Range("R9:C" & u).Value
Set xRng = Sheets("Data").Range("R14:C3", "R" & LastRow & ":C3")
Set CountsRng = Sheets("Data").Range("R14:C" & u, "R" & LastRow & ":C" & u)
' Debug.Print ("CountsRng: R14:C" & u & ", R" & LastRow & ":C" & u & " NameRng: " & NameRng & " xRng: R14:C3 , R" & LastRow & ":C3")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).XValues = xRng
ActiveChart.SeriesCollection(i).Values = CountsRng
ActiveChart.SeriesCollection(i).Name = NameRng
Next i
End Sub
With ActiveChart.SeriesCollection.NewSeries
,然后在以下行中设置.XValues等。 然后用End With
关闭。 - K_B