Ever heard of the Goal Seek feature in Excel? It is a very nice feature wherein it takes out the guesswork for you and determines the input value needed to achieve a specific goal. For example, you have a goal / result in mind, but you are unsure what the starting amount should be for an investment. Let us use Excel Macros to execute the Goal Seek feature!

Make sure your Excel has the Developer Tab enabled following this tutorial.

I explain how you can do this below step by step!

What does it do?

Executes Goal Seek in Excel

Copy Source Code:


'Make sure the worksheet is selected to execute the Goal Seek on
Sub GoalSeekVBA()

Dim TargetGoal As Long

'Get the target value from the user
TargetGoal = InputBox("Enter the target value", "Enter Goal")

'Make sure to change the cell that you want to be changed with the goal
ActiveSheet.Range("E9").GoalSeek _
   Goal:=TargetGoal, _
   ChangingCell:=Range("A9")

End Sub

Final Result: 

Activate R1C1 Reference Style Using Macros In Excel

Exercise Workbook:

Use Goal Seek Using Macros In Excel | MyExcelOnline

DOWNLOAD EXCEL WORKBOOK


This is our scenario. We have a formula calculating our target goal:

  • The initial amount is $5000
  • Interest rate is 4%
  • Number of years is 10
  • Monthly additional investment is $1000
  • With these parameters, after 10 years, your investment will be equivalent to $154,703.97

Activate R1C1 Reference Style Using Macros In Excel

Let us now assume that, given we want to achieve a goal of $250,000. What will our initial amount be?

Take note of the following:

  • Initial Amount – Cell A9
  • Total Amount (Goal) – Cell E9

STEP 1: Go to Developer > Code > Visual Basic

Use Goal Seek Using Macros In Excel | MyExcelOnline

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

Do take note that we are referencing these two cells in the code:

  • Initial Amount – Cell A9
  • Total Amount (Goal) – Cell E9

What goal seek will do, is it will adjust the initial amount (Cell A9), to achieve the target goal that you specify (Cell E9) which is $250,000.

See also  Get the Count of Unsaved Workbooks Using Macros In Excel

Activate R1C1 Reference Style Using Macros In Excel

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Use Goal Seek Using Macros In Excel | MyExcelOnline

 

Make sure your macro is selected. Click Run.

Activate R1C1 Reference Style Using Macros In Excel

Type in the target value of $250,000. See how the initial amount will change.

Activate R1C1 Reference Style Using Macros In Excel

 

With just one click, Goal Seek computed that you need an initial amount of $68,921.35 to achieve your goal of $250,000!

Activate R1C1 Reference Style Using Macros In Excel

 

How to Use Goal Seek Using Macros In Excel

 

101 Macros Book

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin