How to Automate Goal Seek in Excel (2 Easy Methods)

In this article, we’re going to show you how to Automate Goal Seek in Excel. To demonstrate our methods, we’ve selected a dataset with 4 rows: “Base Price”, “VAT, “Service Charge”, and “Total Price”. We’ll change the Total Price to know our Base Price for a particular product. Moreover, “VAT” and “Service Charge” are 10% and 5% of the Base Price respectively.

How to Automate Goal Seek in Excel: 2 Ways

1. Using VBA Code to Automate Goal Seek in Excel

For the first method, we’re going to use an Excel VBA Macro to Automate Goal Seek. Then, we’ll add that code into a VBA Button to perform calculations when clicking that Button. Moreover, our Worksheet name is “VBA Manual” for this method.

Steps:

Firstly, from the Developer tab >>> select Visual Basic.

This will bring up the Visual Basic window. Alternatively, you can press ALT + F11 to do so.

Secondly, from Insert >>> select Module.

We’ll type our VBA code in the Module window.

Thirdly, type the following code in that window.

Sub AutomateGoalSeek()
On Error GoTo Errorhandler
Worksheets("VBA Manual").Activate
    With ActiveSheet.Range("C7")
        .GoalSeek Goal:=Range("C9"), ChangingCell:=Range("C4")
    End With
Exit Sub
Errorhandler: MsgBox ("Error! Invalid Value")
End Sub

VBA Code Breakdown

Firstly, we’re calling our Sub Procedure AutomateGoalSeek.
Secondly, in case of an error, we’re setting an Error handler.
Thirdly, we’re activating our “VBA Manual” Worksheet.
After that, we’ve put the Goal Seek criteria using a With Statement.
Then, we’ve set our target value in cell C9, set cell C4, and by changing cell C4.

Then, Save and close the Module.

Now, we’re going to add a VBA Macro Button. We can click this Button to execute our code.

Firstly, from the Developer tab >>> Insert >>> select Button (Form Control).

Then, our cursor will change.

Secondly, Drag a rectangle in row 10.

The Assign Macro dialog box will appear.

Thirdly, select “AutomateGoalSeek”.
Then, press OK.

We’ll see that a Button is created.

We can Right Click on it to format the Button.

After that, change the value in cell C9.
Finally, click on the Button.

We can see that the prices have changed. Thus, we Automate Goal Seek in Excel using a VBA code.

Moreover, here is our formula used in those cells.

Read More: How to Use Goal Seek in Excel

2. Use of VBA Code to Automate Goal Seek Whenever Any Change Occurs

For the final method, we’ll update the Goal Seek whenever we change our value in the Target Price row. Here, we’ll input our codes into the Sheet (not in the Module).

Steps:

Firstly, press ALT + F11 to bring up the VBA window.
Secondly, Double Click on “Sheet 3 (VBA Dynamic)”.
Thirdly, type the following code.

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

VBA Code Breakdown

Firstly, we’re creating a Private Sub where we’ve chosen Worksheet as General and Change as Declarations.
Secondly, we’re telling it to calculate when any change happens in cell C9.
Finally, we’ve set our target value in cell C9, set cell C4, and by changing cell C4.

Notice, that there is “Change” showing in the upper right corner of our code. That means, it will execute this VBA code, when there is a change. Moreover, we’ve specified the change in cell C9.

After that, Save and close the window.

Now, if we change the value in cell C9, then the entire dataset will change. In conclusion, we’ve shown you yet another method of Automating Goal Seek in Excel.

Here, we’ve changed the value of cell C9 another time, the rest of the values changed too.

Practice Section

We’ve included practice datasets for each method in the Excel file.

Download Practice Workbook

Automate Goal Seek.xlsm

Conclusion

We’ve shown you 2 methods of how to Automate Goal Seek in Excel. If you have any confusion regarding our methods, feel free to comment below for assistance. As always, thanks for reading, and keep excelling!

<< Go Back to Goal Seek in Excel | What-If Analysis in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!