WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Goal Seek

VBA Goal Seek

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Goal Seek in Excel VBA

Goal Seek is the tool available in excel VBA, which helps us to find the required number to be achieved to get to the set target.

For example, you are a student, and you have targeted an average score of 90% from six available subjects. As of now, you have completed 5 exams, and you are left with only one subject. Your anticipated scores from five completed subjects are 89, 88, 91, 87, 89, and 90. Now you want to know how much you need to score in the final examination to achieve the overall average percentage target of 90%.

This can be done by using GOAL SEEK in excel worksheet as well as in VBA coding. Let’s see how it works with VBA.

VBA-Goal-Seek

VBA Goal Seek Syntax

In VBA Goal Seek, we need to specify the value we are changing and arrive at the final targeted result, so supply the cell reference by using the VBA RANGE object. Later we can access the GOAL SEEK option.

Below is the syntax of goal seek in VBA.

Goal Seek Syntax

  • Range(): In this, we need to supply the cell reference where we need to achieve the targeted value.
  • Goal: In this argument, we need to enter what is the goal we are trying to achieve.
  • Changing Cell: In this argument, we need to supply by changing which cell value we need to achieve the goal.

Examples of Excel VBA Goal Seek

The following are the examples of goal seek in Excel VBA.

You can download this VBA Goal Seek Excel Template here – VBA Goal Seek Excel Template

VBA Goal Seek – Example #1

Let’s take the example of an average examination score only. Below is the anticipated score of 5 subjects from the completed exam.

VBA Goal Seek Example 1.1

First, we need to arrive on what is the average score from the completed 5 subjects. Apply the AVERAGE function in the B8 cell.

VBA Goal Seek Example 1.2

In this example, our Goal is 90, and Changing Cell will be B7. So Goal Seek will help us to find the targeted score from the final subject to achieve the overall average of 90.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Start the subprocedure in the VBA class module.

Code:

Sub Goal_Seek_Example1()

End Sub

VBA Goal Seek Example 1.3

Now we need the result in B8 cell, so supply this range reference by using the RANGE object.

Code:

Sub Goal_Seek_Example1()

    Range ("B8")

End Sub

VBA Goal Seek Example 1.4

Now put a dot and enter the “Goal Seek” option.

VBA Goal Seek Example 1.5

The first argument is “Goal” for this. We need to enter our end goal to arrive in RANGE B8. In this example, we are trying to achieve the target of 90.

Code:

Sub Goal_Seek_Example1()

    Range("B8").GoalSeek Goal:=90

End Sub

VBA Goal Seek Example 1.6

The next argument is “Changing Cell” for this, we need to supply in which cell we need the new value to achieve the Goal.

Code:

Sub Goal_Seek_Example1()

    Range("B8").GoalSeek Goal:=90, ChangingCell:=Range("B7")

End Sub

Example 1.7

In this example, our changing cell is Sub 6 cell i.e., B7 cell.

Ok, let’s run the code to see what needs to be done in the final subject to achieve the overall average percentage of 90.

VBA Goal Seek Example 1

So, in the final subject, 95 has to be scored to get the overall average of 90.

VBA Goal Seek – Example #2

We have learned how to apply GOAL SEEK to find the number required to achieve the goal. Now we will see some advanced examples of finding the final examination score for more than one student.

Below are the anticipated scores of 5 subjects after the exam.

Example 2.1

Since we are finding the goal for more than one student, we need to use loops. Below is the code for you.

Code:

Sub Goal_Seek_Example2()

    Dim k As Long
    Dim ResultCell As Range
    Dim ChangingCell As Range
    Dim TargetScore As Integer

    TargetScore = 90

    For k = 2 To 5
        Set ResultCell = Cells(8, k)
        Set ChangingCell = Cells(7, k)
        ResultCell.GoalSeek TargetScore, ChangingCell
    Next k

End Sub

Example 2.2

This code will loop through all the students’ scores and arrive final examination score required to achieve the overall average of 90.

VBA Goal Seek Example 2

So we got the end result now as,

Example 2.3

Student A needs to score just 83 to secure the overall 90 percentage, and Student D needs to score 93.

But look at Student B & C. They need to score 104 each in the final examination, which is not possible at all.

Like this using GOAL SEEK analysis, we can find the required number to achieve the targeted number mid through the project or process.

Things to Remember

  • Goal Seek is available with both worksheet tools as well as the VBA tool.
  • The resulting cell should always contain a formula.
  • We need to enter the goal value and changing the cell reference to the goal seek tool.

Recommended Articles

This has been a guide to VBA Goal Seek. Here we learn how to find the required value to achieve the target value using Goal Seek in Excel VBA with the help of examples and a downloadable excel sheet. You can learn more from the following VBA articles –

  • VBA Update Screen
  • VBA With Statement
  • UDF in Excel VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Goal Seek Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More