sorting multiple trendline equations using VBA

timfrey1

New member
Joined
Feb 16, 2021
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Microsoft Excel for Office 360
(My first post)
In VBA I created a chart with 2 sets of data and added trendlines with automatic display of the equations and R-square values. However VBA always put the equations and R-square on top of each other and When I manually separate them I am not sure which trendline equation goes with which set of data, especially if the 2 sets of data are fairly similar. Subtle equation coefficients are critical to my analysis. Is there anyway I can move the first trendline equation before the second trendline equation is put on the graph? Thanks in advance for any reply. Stay safe!
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,149
Reaction score
1
Points
38
Excel Version(s)
365
Attach a small workbook with your existing code and charts - it may just be a tweak - that would:
  • save us guessing wrongly what's in your file
  • give us something to experiment with
  • make it more likely you get the most appropriate response
 

timfrey1

New member
Joined
Feb 16, 2021
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Microsoft Excel for Office 360
current snip of code

Attach a small workbook with your existing code and charts - it may just be a tweak - that would:
  • save us guessing wrongly what's in your file
  • give us something to experiment with
  • make it more likely you get the most appropriate response

Below is a snippet of the code. I'm not proficient. I could place the chart where I want it but cant figure out how to place the trendline equation and R-square values. Since I plot 2 sets of data, I need 2 trendline equations. The code just places one on top of the other in the chart. Thanks.


' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=350, Width:=600, Top:=60, Height:=300)
With myChtObj.Chart


' make an XY chart
.ChartType = xlXYScatterLines
.HasTitle = True
.ChartTitle.Caption = Range("$B$1")
.Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = False
.Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = False
.Axes(xlValue, xlPrimary).MinimumScaleIsAuto = True
.Axes(xlValue, xlPrimary).MaximumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).MaximumScale = SampleLen
.Axes(xlCategory, xlPrimary).MinimumScale = 0

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Caption = "Length of Shade (in.)"
.Axes(xlValue, xlPrimary).AxisTitle.Caption = "Force (grams)"


With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(0, 1).Resize([StepTotal], 1)
.XValues = rngChtXVal.Resize([StepTotal], 1)
.Name = "Extension"
.Trendlines.Add Type:=xlPower, Name:="Ext Trend"


End With
With .SeriesCollection.NewSeries
.Values = rngChtXVal2.Offset([StepTotal], 1).Resize([StepTotal], 1)
.XValues = rngChtXVal2.Offset([StepTotal], 0).Resize([StepTotal], 1)
.Name = "Retraction"
.Trendlines.Add Type:=xlPower, Name:="Ret Trend"

End With
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,149
Reaction score
1
Points
38
Excel Version(s)
365
Attach a small workbook
Come on, please. No one's going to start putting together dummy data and then a chart in the hope that they get it right. You'll get the best advice/solution for your problem if you attach an Excel workbook.
If the stuff is sensitive, alter the data so it no longer is, while retaining realism.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,149
Reaction score
1
Points
38
Excel Version(s)
365
By the way,
Subtle equation coefficients are critical to my analysis.
are you intending to extract these values from the chart trendline label? There is a way to get these data easily and accurately without using a trendline label (using LINEST), also without even using a chart.
 
Last edited:
Top