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 fin the Base Price of a product (“VAT” and “Service Charge” are 10% and 5% of the Base Price).

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

Use an Excel VBA Macro.

Steps:

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

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

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 Manual” Worksheet.
Sets the Goal Seek criteria using a With Statement.
Sets the target value in C9, by changing C4.

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.

The Button is created.

Right-click the button to format it.

Change the value in C9.
Click the Button.

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

Update the Goal Seek whenever the value in the Target Price row changes. Enter the codes in the Sheet (not in the Module).

Steps:

Press ALT + F11 to open the VBA window.
Double-click “Sheet 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.