Call GoalSeek from a user-defined-function
 

Hello,
I am trying to call GoalSeek within a function, but GoalSeek appears to
only work if called from a sub. For example, calling the function below from
a cell will not update any cells, but having a button call the function will.
Is there some contraint that GoalSeek must be triggered by a sub instead of
being called from a user-defined function?

Function TestGoalSeek(rTarget As Range, rChange As Range, dblGoal As Double)
As Double
Call DoGoalSeek(rTarget, rChange, dblGoal)
TestGoalSeek = rChange(1, 1)
End Function

Private Sub DoGoalSeek(rTarget As Range, rChange As Range, dblGoal As Double)
'Range("F11").GoalSeek Goal:=10, ChangingCell:=Range("E11")
Dim b As Boolean
b = rTarget.GoalSeek(dblGoal, rChange)
End Sub

Thanks,
Mark

Call GoalSeek from a user-defined-function
 

User defined functions called from a worksheet can only return a value to
the cell in which they are entered. So it would be no surprise that
goalseek would not work from a UDF.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
Hello,
I am trying to call GoalSeek within a function, but GoalSeek appears to
only work if called from a sub. For example, calling the function below
from
a cell will not update any cells, but having a button call the function
will.
Is there some contraint that GoalSeek must be triggered by a sub instead
of
being called from a user-defined function?

Function TestGoalSeek(rTarget As Range, rChange As Range, dblGoal As
Double)
As Double
Call DoGoalSeek(rTarget, rChange, dblGoal)
TestGoalSeek = rChange(1, 1)
End Function

Private Sub DoGoalSeek(rTarget As Range, rChange As Range, dblGoal As
Double)
'Range("F11").GoalSeek Goal:=10, ChangingCell:=Range("E11")
Dim b As Boolean
b = rTarget.GoalSeek(dblGoal, rChange)
End Sub

Thanks,
Mark