Maarten -

If you would prefer a worksheet approach instead of VBA, you could use a
combination of the EXP, LN, SLOPE, and INTERCEPT worksheet functions to get
the parameters of the trendline.

For example, for the power function y=c*x^b,
c = EXP(INTERCEPT(LN(Yrange),LN(Xrange)))
b = SLOPE(LN(Yrange),LN(Xrange))

- Mike

www.mikemiddleton.com

++++++++++++++++++++

"Maarten" wrote in message
...
In quite a few of my excel projects I use trendlines to predict values.
Currently I manually input the trendline formula into my calculations. But
every time the trendline gets updaten (by adding more data to my database)
I
have to manually change the formula in my cells. Currently I'm trying to
automate that.

Since there didn't seem to be a direct/straightforward way of doing this,
I've recorded a macro that extracts the formula and pastes it in a cell.
This
works without problems. The formula appears as follows:

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

next I wabt to replace x with a cell, so "x" is automatically replaced by
"*E72^", this also works without problems, now the value of my target cell
looks like this:

y = 136,56*E72^-0,0929

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
but I just can't seem to be able to get it to work automatically.

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).Data Label.Select
Formule = CStr(Selection.Caption)
ActiveWindow.Visible = False
Windows("filename.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
__________________________________________________ ______

Again, I might be doing this the hard way, so if there is an easier way
I'd
like to know :)

PS, I hope everything is clear since English isn't my native language (and
that explains the , and . mixups as well ;) )