Excel 03 Autorun GoalSeek on calculation bug

Topic

New Reply

I am having trouble with some code which is supposed to cause Goal Seek to run any time there is a calculation on the sheet.

I found this code on the web, modified it to use named cells, and inserted in a worksheet module (not a normal module):

Private Sub Worksheet_Change(ByVal Target As Range)

Range(“Goal”).GoalSeek Goal:=0, ChangingCell:=Range(“changing_cell”)

End Sub

In this code, “Goal” is the “Set Cell” in goal seek
It is supposed to be set to a value of 0.
“Changing_cell” is the name of the cell to be changed to make the “Goal” = 0.

I found that it worked on a very simple model; but now in an only slightly more complex one, it doesn’t seem to work at all.(calculation is set to automatic, by the way)

Attached are the working and buggy examples. Both are very simple and I have taken pains to make the problem clear with formatting and notes. The user-changeable variables are in blue font; any time one of these changes, the Goal Seek macro is supposed to change. Can the code in the second model be fixed so that the Goal Seek macro runs anytime there’s a change on the worksheet?

I’d be very grateful for any help. If this could be made to work it would be very useful.

By the way, I am pretty weak in Excel VBA, so please bear that in mind in any reply !

Regards

lingyai

(PS You don’t need to worry about what the models actually calculate — though if you are interested, it is to calculate the size of identical annual payments to be made for so many years, given the number of years, the interest rate and the amount of money needed at the end of those years; but for specific reasons I want to use Goalseeek, not a formula or one of Excel’s native functions. )

Reply | Quote

Replies

Your goal seek cell (E19) does not have a formula in it. So changing other cells will not have any effect on that cell.

Reply | Quote

The code is not running when calcuations are being made, it runs when a cell or cells triggers a change.

The spinners you are using will NOT trigger a change event so do not trigger the code to run.

You can get the spinners to recalc goal seek by creating the goal seek code in module and have the change event call the code and also assign the macro to the spinner event.

Alternately you could assign the code to the calculation event.

Also I would add the line Whether in a change or calc event):
before running the code and then after the code add the line:
Application.EnableEvents = true

to prevent recursive calls to the routine when the code changes the cells…

Steve

Reply | Quote