Using Trendline Formula

[Maarten]

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 )

 

[Tom Ogilvy]

If you have replace all cells containing "y ="

then this line will cause an error:

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

the other problem could be the fact that you are using comma as your
delimiter. Often, when VBA is involved, you have to use US formatting. Try
replacing the "," with "." before you replace the "y = "

 

[Michael R Middleton]

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

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