vba - 使用 VBA 代码在 Excel 中创建组合图表

Question

如何调整下面的代码来创建一个以条形为主 Axis 、以线条为次 Axis 的组合图表？

我有两列数据。

Sub CreateChart()
    Dim rng As Range
    Dim cht As Object

    Set rng = ActiveSheet.Range("C1:D6")
    Set cht = ActiveSheet.Shapes.AddChart2

    cht.Chart.SetSourceData Source:=rng

    cht.Chart.ChartType = xlColumnClustered

    cht.Chart.HasTitle = True
    cht.Chart.ChartTitle.Text = "Average Price and Dollar Volume of Sales"

End Sub

如有任何帮助，我们将不胜感激!谢谢!

Answer 1

这个怎么样:

Sub foo()
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$C$1:$D$6") 'make sure the range is correct here
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered 'select which column should be the Line or the Column
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).ChartType = xlLine
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    ActiveChart.ChartTitle.Text = "Average Price and Dollar Volume of Sales"
End Sub