如何使VBA重复为Excel图形创建系列，直到最后一行数据

Question

我正在尝试使VBA根据一组数据中的列自动创建图形。行数随加载的数据而变化。

我只是无法让它使用下一个函数正确选择数据，因为当我手动填写 SeriesCollection 时它确实有效。

感谢您的任何帮助

Sub generate_PrimaryBubble()
Dim ochartObj As ChartObject
Dim oChart As Chart
Set ochartObj = ActiveSheet.ChartObjects.Add(Top:=100, Left:=200, Width:=500, Height:=300)
Set oChart = ochartObj.Chart
oChart.ChartType = xlBubble
Set CategoryRange = ActiveSheet.Range("B" & CStr(FirstRow) & ":B" & CStr(lastrow))
Set ItemEfficiencyRange = ActiveSheet.Range("I" & CStr(FirstRow) & ":I" & CStr(lastrow))
Set TotalUPCRange = ActiveSheet.Range("I" & CStr(FirstRow) & ":I" & CStr(lastrow))
    oChart.SetSourceData Source:=CategoryRange
'SeriesCollection Counting
    Dim i As Long
    lastrow = Range("N" & Rows.Count).End(xlUp).Row
    MsgBox "Last Row: " & lastrow & vbNewLine

'SeriesCollection Creation
For i = 2 To lastrow
    oChart.SeriesCollection(i).XValues = Range("N" & i)
    oChart.SeriesCollection(i).Values = Range("H" & i)
    oChart.SeriesCollection(i).Name = Range("B" & i)
    oChart.SeriesCollection(i).BubbleSizes = 1
Next i


'Graph Labels
    oChart.Axes(xlCategory).HasTitle = True
    oChart.Axes(xlCategory).AxisTitle.Caption = "Efficiency"
    oChart.Axes(xlValue).HasTitle = True
    oChart.Axes(xlValue).AxisTitle.Caption = "Total #"
'Variable MaximumScales
    oChart.Axes(xlValue).MaximumScale = 1000000
    oChart.Axes(xlValue).MinimumScale = 0
    oChart.Axes(xlPrimary).MaximumScale = 1
    oChart.Axes(xlPrimary).MinimumScale = 0
    oChart.Axes(xlPrimary).TickLabels.NumberFormat = "0%"

End Sub

Answer 1

尝试在for循环中创建新系列。您需要先添加一个系列，然后才能设置其值和 xvalue。您有一个for循环，您可以在其中设置多个序列的值;因此，您需要创建多个序列，最好在for循环开始时完成：

Sub generate_PrimaryBubble()
    Dim ochartObj As ChartObject
    Dim oChart As Chart
    Set ochartObj = ActiveSheet.ChartObjects.Add(Top:=100, Left:=200, Width:=500, Height:=300)
    Set oChart = ochartObj.Chart
    oChart.ChartType = xlBubble
    Set CategoryRange = ActiveSheet.Range("B" & CStr(FirstRow) & ":B" & CStr(lastrow))
    Set ItemEfficiencyRange = ActiveSheet.Range("I" & CStr(FirstRow) & ":I" & CStr(lastrow))
    Set TotalUPCRange = ActiveSheet.Range("I" & CStr(FirstRow) & ":I" & CStr(lastrow))
    oChart.SetSourceData Source:=CategoryRange
'SeriesCollection Counting
    Dim i As Long
    lastrow = Range("N" & Rows.Count).End(xlUp).Row
    MsgBox "Last Row: " & lastrow & vbNewLine
'SeriesCollection Creation
    For i = 1 To lastrow - 1
        oChart.SeriesCollection.NewSeries
        oChart.SeriesCollection(i).XValues = Range("N" & i + 1)
        oChart.SeriesCollection(i).Values = Range("H" & i + 1)
        oChart.SeriesCollection(i).Name = Range("B" & i + 1)
        oChart.SeriesCollection(i).BubbleSizes = 1
        Next i
'Graph Labels
        oChart.Axes(xlCategory).HasTitle = True
        oChart.Axes(xlCategory).AxisTitle.Caption = "Efficiency"
        oChart.Axes(xlValue).HasTitle = True
        oChart.Axes(xlValue).AxisTitle.Caption = "Total #"
'Variable MaximumScales
        oChart.Axes(xlValue).MaximumScale = 1000000
        oChart.Axes(xlValue).MinimumScale = 0
        oChart.Axes(xlPrimary).MaximumScale = 1
        oChart.Axes(xlPrimary).MinimumScale = 0
        oChart.Axes(xlPrimary).TickLabels.NumberFormat = "0%"
End Sub