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

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 Whe=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(Typ e:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Sele ct
With Selection.Border
.ColorIndex = 6
.Weight = xlMedium
.LineStyle = xlContinuous
End With

ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Trendlines.Add(Typ e:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.SeriesCollection(2).Trendlines(1).Sele ct
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

The code is inefficient, but I could find nothing that would cause this
problem. I know sometimes that people have reported problems with secondary
axis trendlines being plotted on the primary axis, but that didn't happen
when I made up some dummy data and ran your code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
oups.com...
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 Whe=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(Typ e:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Sele ct
With Selection.Border
.ColorIndex = 6
.Weight = xlMedium
.LineStyle = xlContinuous
End With

ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Trendlines.Add(Typ e:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.SeriesCollection(2).Trendlines(1).Sele ct
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