Call GoalSeek from a user-defined-function

M

Mark

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
 
T

Tom Ogilvy

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top