VBA – Using Goal Seek in VBA

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on July 19, 2021

This tutorial will show you how to use Goal Seek in VBA

Goal Seek is one of the What -if analysis tool available in Excel which allows you to apply different values to formulas and compare the results.

Goal Seek Syntax

vba goal seek syntax

The function GoalSeek has 2 arguments – the goal (the cell you want to change), and ChangingCell (the cell that needs to be amended).  The function returns a True or a False – True if the Goal is found, and False if the Goal is not found.

Using Goal Seek in VBA

Consider the following worksheet below.

vba goal seek example 1 before

To change the repayment required each month by changing the term in months, we can write the following procedure.

Sub IncreaseTerm()
'make B6 100 by changing B5
   Range("B6").GoalSeek Goal:=100, ChangingCell:=Range("B5")
End Sub

Note that there has to be a formula in cell B6, and a value in cell B5.

Using Goal Seek with an If Statement

You can also use GoalSeek with an If statement to direct the flow of your code.

Sub IncreaseTerm()
'make B6 100 by changing B5 - and return a message to the user
   If Range("B6").GoalSeek(Goal:=100, ChangingCell:=Range("B5")) = True Then
      MsgBox "New Term was found successfully"
      MsgBox ("New Term was not found")
   End If
End Sub

vba goal seek if statement

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples