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.
Download Practice Workbook
2 Ways to Automate Goal Seek in Excel
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.
- 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 Errorhandler.
- 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 are changed. Thus, we Automate Goal Seek in Excel using a VBA code.
Moreover, here is our formula used in those cells.
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).
- 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.
We’ve included practice datasets for each method in the Excel file.
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!