How to Get Goal Seek or Solver to Continuously

Goal Seek runs calculations repeatedly (iteratively) to find possible outcome values for your inputs. In this guide, we're going to show you how to automate Goal Seek in Excel.

Download Workbook

Scenario

Let's take a look at a basic example. There are two inputs (input1 and input2) and the output equals sum of the square of input1 and input2.

Let's assume the input2 is equal to 3, and we want the output to be 39. We can find the value of the input1 with Goal Seek.

How to automate Goal Seek

The only way to automate Goal Seek is using VBA. The easiest way of using VBA is by recording a macro. If you're not familiar with this concept, a macro is a feature that creates a VBA code. You can record almost any action. and use this recording as a VBA cheat sheet.

First, you will need to access the Developer tab in the ribbon. You can make it visible by enabling from Excel Options > Customize Ribbon.

For more details see How to display the Excel Developer tab for using advanced features.

Record a macro

1. Click Record Macro in the Developer
2. (Optional) Give a friendly name and click OK to start recording.
   
3. Open the Goal Seek window by following Developer > What-If Analysis > Goal Seek.
4. Enter your inputs:
   1. Set Cell: C6 (formula cell)
   2. To value: 39 (desired output)
   3. By changing cell: C4 (input1)
5. Click OK to run Goal Seek.
   
6. Click OK on Goal Seek Status dialog.
   
7. Stop the recording by clicking Stop Recording in the Developer The same Record Macro button.
8. Open VBA by clicking Alt + F11 if it isn't opened automatically.
9. Open the Modules and double-click on the latest module, which is Module 1 in this example. You will see the code on the right editor panel.
   

Thanks to the macro recording we can get the code without any programming. Now, we can automate Goal Seek using this code line.

Run Goal Seek upon value change

To automate Goal Seek and run it again when a value is changed, the code needs to trigger after the value of a specific cell is changed. This means that you need to place the code in a worksheet's Change event.

1. Double-click on the worksheet name on the left pane (This is where you click the module).
2. Select Worksheet from the left dropdown on the right pane. This action will automatically add the Worksheet_SelectionChange routine for SelectionChange. You can ignore this.
3. Select Change in the second dropdown for Change.
4. Copy and paste the Goal Seek code in the module.
   

If you leave VBA as is and continue to the worksheet, you will see that the Goal Seek runs after any value is changed in any cell.

You can refine the code to run after changes to specific cells only. To apply this condition, you need to create a logical test. You can use the Application.Intersect method within an If statement to do this. The following code block runs Goal Seek if the cell C5 (input2) is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("input2")) Is Nothing Then
Range("C6").GoalSeek Goal:=39, ChangingCell:=Range("C4")
End If
End Sub

Set a dynamic goal

Our examples have had a static number for the Goal argument in the previous examples. You can set a value for this cell - instead of a static value - to run the Goal Seek for the cell value. If we assume the cell C6 contains the desired goal value. The code should be like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("input2, C6")) Is Nothing Then
Range("C7").GoalSeek Goal:=Range("C6"), ChangingCell:=Range("C4")
End If
End Sub

The code below was updated to run Goal Seek after either input2 or C6 cells and the Goal was set as cell C6.

whitakertagathe93.blogspot.com

Source: https://www.spreadsheetweb.com/how-to-automate-goal-seek-in-excel/

Share this post