Using trendline formulae

[Maarten]

In some graphs I've made trendlines, the formula of the trendline is shown as a caption in the graph window.

I would like to be able to directly use this formula in my calculations, so that if the trendline changes (by adding data to the database) that the new formula is automatically used.

Since there didn't seem to be a direct/straightforward way to do this, I've recorded a macro that extracts the formula and pasts it in a cell. This works without problems. The formula is is in the following form:

y = 136,56x-0,0929 (with -0,0929 being the power)

next in this formula is replace x with a cell, so "x" is replaced by "*E72^", this also without problems.

finally, I want to replace "y = " with "=". As soon as I do this, I get a Error 1004 (typed formula contains an error).

If I manually replace "y = " with "=", the calculations go without problems.

Could anyone help me out?

this is my code sofar:

Sub UitlezenTrendlijnformule()

Dim Formule As String

ActiveSheet.ChartObjects("Grafiek 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
Formule = CStr(Selection.Caption)
ActiveWindow.Visible = False
Windows("investeringsraming DGMR.xls").Activate
Range("F72").Select
ActiveCell.Value = Formule

Range("F72").Select
ActiveCell.Replace What:="x", Replacement:="*E72^", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:="x", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

Range("F72").Select
ActiveCell.Replace What:="y = ", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:="y = ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

End Sub

[Maarten]

if anyone knows of an easier way to use trendline formulas then that would be great too

[Maarten]

argh, going insane