Create own function performing GoalSeek calculations

[altren]

I'm trying to write a function that adjusts a parameter using GoalSeek with the "setCell", "changingCell" and "Goal" as input parameters:

Function Adjust (SetCell As Range, GoalValue As Double, ChangingCell As Range) As Boolean
   Adjust = SetCell.GoalSeek(GoalValue, ChangingCell)
End Function

However, the above function simply returns "FALSE" and no GoalSeek calculations are performed . When I do the exact same thing with Excel's standard Goal Seek inputbox, it works. I have checked that SetCell.Address, ChangingCell.Address and GoalValue correspond to the input values I use in the standard inputbox.

Any clues on why this doesn't work? Many thanks in advance!

 

[wigi]

Functions cannot do these kind of things.

Most of the time, they can only return a value (boolean, text, number, ...) or return a message box.

As opposed to executing a method like GoalSeek.

Wim

 

[altren]

Thanks for your response!

The (working) solution I used before was a loop that performed GoalSeek calculations on a range of cells. The reason I wanted to put the GoalSeek method in a function is that I don't want the user to have to press a button to get the calculations done, but rather have the cells updated automatically when the input values change.

Can I do this in some other way?

 

[wigi]

Then you could use a Worksheet_Change event that will fire on changing certain cells.

You can put it in the function, which is called during the event, to capture the True or False result. If the result would be False (as the result of some kind error), you could take additional steps. If it's True, you just continue.