CODEWITHSUNDEEP
vbaexcelaxis
K.R.
K.R.

Reputation: 21

Creating a combo chart in Excel with VBA code

How do I adjust the code below to create a combo chart with a bar for the primary axis and line for the secondary axis?

I have two columns of data. 

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

Any help would be appreciated! Thank you!

Upvotes: 2

Views: 11254

Answers (1)

Xabier
Xabier

Reputation: 7735

How about this:

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

Upvotes: 5

Related Questions