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
VBA Coding Made EasyStop 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!