That did not cause GS to misbehave in my Excel version. When you run the macro it asks for the following: 1.
However, I was wondering if anyone knew how to change the Macro so that instead of asking for the range of cell values to set to it just asked for a single value. ( (Range)rgout.Cells 4, 2).GoalSeek ( 0, rgout.Cells 3,5). However, if GS consistently misbehaves with your example, take a look at the Excel Advanced options.įor example, I tried enabling Iterative Calculation with Max Iterations set to 1 (!) in an attempt to cause GS to fail early. I found the below Macro to do goal seek on multiple cell ranges and it works really well. In this c example, the formula that returns the target value is in the Cells 4,2, the value to be adjusted is in Cells 3,5 and the target value is zero. None of us knows anything about the internal implementation GS. Go to Data tab and click on What if analysis and select goal seek. In the resulting dialog, enter 50000 as the To value. In Excel 2003, choose Goal Seek from the Tools menu. That is just wild speculation, of course. Now, we will use goal seek to find out how much cell A5 should have so that A6 will be calculated to the corpus amount required. Click the Data tab, click What-If Analysis, and then choose Goal Seek. And I do not believe that GS uses the target cell to temporarily store a constant.īut perhaps it does and circumstances cause GS to abort the operation, failing to restore the target formula. I cannot think of any reason why GS would replace the target (Set) cell with a constant. How did you get from there to the second image, with 1.01 in A1? Is that simply a different starting value that you entered manually?įYI, I did not try that, and Goal Seek still not misbehave. Your first image suggests that you enter 1 into A1, 2 into B1 and =A1+B1 into C1, then you do a Goal Seek to set A1 to 5 by changing C1. Not surprisingly, I cannot reproduce the problem with your simple example, using Excel 2010. I don't believe that sharing any product version information will get you closer to an explanation, much less a solution.Ī google search reveals that problems like this have been reported with Excel 2007, which predates the subscription services.