VBA – Using Goal Seek in VBA

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

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.

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"
   Else
      MsgBox ("New Term was not found")
   End If
End Sub