sorting multiple trendline equations using VBA

[timfrey1]

(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]

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]

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]

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]

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.