Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"line chart" won't display lines | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Lines in line chart are "agglomerating" data | Charts and Charting in Excel | |||
How do I format the x axis on a "lines on 2 axes" chart? | Charts and Charting in Excel | |||
Showing "Meeting point" of lines in Chart | Excel Discussion (Misc queries) |