Run goal seek based on changing cell value (that is a formula result)

[Phillycheese5]

Hi,
I have a macro to run goal seek: set B23=0 by changing cell B16. I have that macro run automatically when changes are made to cell B15. This works when I type a number into B15 but when I use a formula based on other cells, the macro gives an incorrect result.

Please Login or Register  to view this content.

Any suggestions would be appreciated.

Thanks,

Phillycheese

[Alf]

Perhaps like this then:

First you need to save the value in cell B15 when you open the workbook.

Please Login or Register  to view this content.

then you check if the value in B15 changes

Please Login or Register  to view this content.

and finally you add your SetToZero macro

Please Login or Register  to view this content.

Add the macros save the workbook and close it. When you open it the first macro fires and then the second macro will check if B15 value is changed whenever you do something that fires the recalculation of the worksheet. If then the B15 value is different from the original one the third macro will run

Alf

[Phillycheese5]

Hey Alf,
I appreciate the code. I got a compile error that the "B15" is not defined (the debug highlights it in the second macro). I don't know how to adjust for this but hopefully once that's set it will work. If you have a moment to assist that would be great.
Thanks again,
Phillycheese

[Alf]

OK I did a bit of testing and this works for me.

Please Login or Register  to view this content.

and you can condense the last code so you only need two of them.

Please Login or Register  to view this content.

Hopefully this works for you as well.

Alf

[Phillycheese5]

Hey Alf,
I appreciate the effort! It works! I deleted the "Option Explicit" I had at the top which may have caused the earlier issue. My data is set up for one employee in column B and I was thinking of including more employees, each with their own data in a separate column. If I'm not imposing (might be too late) would you mind offering a suggestion on how to tweak the code to handle multiple goal seeks that go from column B to column P?
Thanks,
Phillycheese

[Alf]

offering a suggestion on how to tweak the code to handle multiple goal seeks that go from column B to column P?

Well now GoalSeek is really not my cup of tea and multiple GoalSeek as far as I know is not possible but I could be wrong of course.

I think you best option is mark this thread "Solved" and start a new thread where you ask about multiple GoalSeek solutions. A new thread always attracts much more interest than an old one where there has been some action by forum members.

There is also the Excel add-in Solver perhaps this could be used in order to solve you problem? I would recommend you google for Excel Solver to see if this could be a useful tool.

Alf

Ps how to mark a thread "Solved"

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

[Phillycheese5]

Sounds like a good plan :-) Again, I appreciate the help!

[Alf]

You are welcome and thanks for feedback

Alf