Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default Help on adding the second trendline in a "lines on 2 axes" Chart via VBA

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"line chart" won't display lines [email protected] Excel Discussion (Misc queries) 3 April 22nd 23 06:07 AM
change "true" and "false" to "availble" and "out of stock" inthestands Excel Worksheet Functions 2 July 19th 07 07:05 PM
Lines in line chart are "agglomerating" data chowgirl Charts and Charting in Excel 3 March 9th 07 06:44 PM
How do I format the x axis on a "lines on 2 axes" chart? Becky B Charts and Charting in Excel 1 April 5th 06 10:23 PM
Showing "Meeting point" of lines in Chart jinjon Excel Discussion (Misc queries) 3 November 19th 05 05:39 PM


All times are GMT +1. The time now is 11:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"