Primary and Secondary axis VBA

Question

I am attempting to plot two time series on Primary and Secondary axis on one chart with following code:

   Set ARng_1 = range("Annual_Series_1")
   Set MRng_1 = range("Monthly_Series_1")
   Sheet10.ChartObjects("Chart 17").Activate
   With ActiveChart
  .ChartType = xlLineMarkers
  .SeriesCollection.Add Source:=ARng_1
  .SeriesCollection.Add Source:=MRng_1
  .Axes(xlCategory, xlPrimary).HasTitle = True
  .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
  .SeriesCollection(1).Axes(xlValue, xlPrimary).HasTitle = True
  .SeriesCollection(2).Axes(xlValue, xlSecondary).HasTitle = True
  .SeriesCollection(1).Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Annual"
  .SeriesCollection(2).Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Monthly"
  .Axes(xlCategory).HasMajorGridlines = True
  .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
  .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
  .SeriesCollection(1).XValues = Date_Rng
  End With

I keep getting an error ("object doesn't support this property or object") at the line:

  .SeriesCollection(1).Axes(xlValue, xlPrimary).HasTitle = True

What am I doing wrong here? Also, am I treating the axis correctly? Am I going about this correctly?

Thanks!!!!!

SeriesCollection(1) represents a Series object. You're getting the error because the Series object doesn't have an Axes member. You can access the axes with Chart.Axes. You can set primary versus secondary axes for a series with Series.AxisGroup. — xidgel

Dy.Lee Dy.Lee · Accepted Answer · 2017-06-07T01:20:09

I think yourt code is to be like bellow..

   Set ARng_1 = Range("Annual_Series_1")
   Set MRng_1 = Range("Monthly_Series_1")



      With ActiveChart
        For i = .SeriesCollection.Count To 1 Step -1
              .SeriesCollection(i).Delete
        Next i
        .ChartType = xlLineMarkers
        .SeriesCollection.Add Source:=ARng_1
        .SeriesCollection.Add Source:=MRng_1
        .SeriesCollection(2).AxisGroup = 2
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Annual"
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Monthly"
        .Axes(xlCategory).HasMajorGridlines = True
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
        .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
        .SeriesCollection(1).XValues = date_rng
      End With

Primary and Secondary axis VBA

1 Answers