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


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.

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.

how to automate goal seek in excel

  • 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.

how to automate goal seek in excel

  • 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.

how to automate goal seek in excel

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.

how to automate goal seek in excel

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).

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.

how to automate goal seek in excel

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.

how to automate goal seek in excel


Practice Section

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


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!

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo