Help on adding the second trendline in a "lines on 2 axes" Chart by using VBA

[changyutung]

Hi,
I have a problem to add the the second trendline in a "lines on 2
axes" Chart by using VBA.
The result is snapshot here. http://www.flickr.com/photos/41664082@N00/1078015474/

As you see from this picture, the red trendline did not appear. In
addition, even though I want to add the second trendline manually, I
can not. I am not sure the reason.

Here is the VBA code :

Private Sub Draw_Graph_1()
Dim RangeY1, RangeY2, RangeX As String
Dim WS1 As String

WS1 = ActiveSheet.Name
RangeX = "=" & WS1 & "!R" & d1 & "C1:R" & d2 & "C1"
RangeY1 = "C" & d1 & ":C" & d2
RangeY2 = "=" & WS1 & "!R" & d1 & "C7:R" & d2 & "C7"

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="lines
on 2 axes"
ActiveChart.SetSourceData Source:=Sheets(WS1).Range(RangeY1),
PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = RangeX
ActiveChart.SeriesCollection(1).Name = "=""Revenue"""
ActiveChart.SeriesCollection(2).XValues = RangeX
ActiveChart.SeriesCollection(2).Values = RangeY2
ActiveChart.SeriesCollection(2).Name = "=""Search"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=WS1
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Revenue"
.SeriesCollection(2).AxisGroup = 2
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"Search"
End With

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection.Border
.ColorIndex = 6
.Weight = xlMedium
.LineStyle = xlContinuous
End With

ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Trendlines.Add(Type:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.SeriesCollection(2).Trendlines(1).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With

End Sub

Can any excel VBA master help me on this? Thank you.

- Tony

 

[changyutung]

Hi,
I found the solution.

remove ActiveChart.SeriesCollection(2).XValues = RangeX, and then it
works.

I am not sure is there any other solutions. but it is solved.

- Tony