How to Automate the Goal Seek in Excel – 2 Methods

The dataset showcases “Base Price”, “VAT, “Service Charge”, and “Total Price”.

Change the Total Price to find the Base Price of a product (“VAT” and “Service Charge” are 10% and 5% of the Base Price).

how to automate goal seek in excel


Method 1 – Using a VBA Code to Automate the Goal Seek in Excel

Steps:

  • Go to the Developer tab >>> select Visual Basic.

The Visual Basic window will be displayed. Alternatively, press ALT + F11.

how to automate goal seek in excel

  • In Insert >>> select Module.
  • Enter the VBA code in the Module 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

  • Calls the Sub Procedure AutomateGoalSeek.
  • Sets the Error handler.
  • Activates the “VBA ManualWorksheet.
  • Sets the Goal Seek criteria using a With Statement.
  • Sets the target value in C9, by changing C4.

how to automate goal seek in excel

  • Save and close the Module.

Add a VBA Macro Button to execute the code.

  • In the Developer tab >>> Insert >>> select Button (Form Control).

The cursor will change.

  • Draw a rectangle in row 10.

The Assign Macro dialog box will appear.

  • Select “AutomateGoalSeek”.
  • Click OK.

how to automate goal seek in excel

The Button is created.

Right-click the button to format it.

  •  Change the value in C9.
  • Click the Button.

how to automate goal seek in excel

This is the output.

These are the formulas:

Read More: How to Use Goal Seek in Excel


Method 2 – Use a VBA Code to Automate the Goal Seek Whenever a Change Occurs

Enter the codes in the Sheet (not in the Module).

Steps:

  • Press ALT + F11 to open the VBA window.
  • Double-clickSheet 3 (VBA Dynamic)”.
  • Use 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

  • A Private Sub is created, declaring Worksheet as General and Change as Declarations.
  • Calculates changes in C9.
  • Sets the target value in C9, by changing C4.
  • Save and close the window.

how to automate goal seek in excel

If the value in C9 changes, the entire dataset will change.

The value of C9 changed, the rest of the values also changed.

how to automate goal seek in excel

Read More: Excel Macro to Goal Seek for Multiple Cells


Practice Section

Practice here.


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo