EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Coding VBA Goal Seek
 

VBA Goal Seek

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Goal Seek

Goal Seek in VBA

Goal Seek in VBA is the process by which we can target the goal which we want to achieve. This is a process like which shows how much we need to do to achieve the target set. Suppose, a train has the arrival time of 10 AM daily. The train runs from Monday to Friday between A and B. And considering the average arrival time of that train from A to B is exactly 10 AM without any delay. So if the following are the arrival time of that train 10:16 AM, 10:01 AM, 9:57 AM, 10:04 AM for consecutive 4 days from Monday to Thursday. And to maintain the average time of 10 AM which is the target set for that train, it needs to arrive at 9.45 AM, just to maintain the average time of arrival of 10 AM. This could be even cooler if we have something to automate with. There comes VBA Goal Seek.

 

 

Syntax for Goal Seek:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Below is the syntax of VBA Goal Seek.

Syntax of GoalSeek

Where,

  • Range: The target range cell which contains the current limit.
  • Goal: The limit or target which we want to achieve.
  • Changing Cell: The cell where we will see the required limit to be achieved.

How to Use Goal Seek in Excel VBA?

We will learn how to use Goal Seek in Excel by using the VBA Code.

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

VBA Goal Seek – Example #1

The average accuracy that employees should maintain its 95% weekly. Below we have the accuracy data of the employee from Monday to Thursday with an average accuracy of 92.25% which is mentioned at cell C8 as shown below.

VBA Goal Seek Example 1-1

Now, as per syntax of Goal Seek, we have Range cell which has the current value to feed and Target value which is the average accuracy of 90% which is to be achieved by that employee.

In this example, where an employee’s accuracy of work in being monitored. For this, follow the below steps:

Step 1: Open Module from the Insert menu tab as shown below.

Insert Module

Step 2: Now write the Sub procedure of VBA Goal Seek as shown below. We can choose any name to define this.

Code:

Sub Goal_Seek()

End Sub

VBA Goal Seek Example1-2

Step 3: As per syntax, we will select the Range cell which contains the current average to be fed. Here that cell is C8.

Code:

Sub Goal_Seek()

Range("C8").

End Sub

VBA Goal Seek Example1-3

Step 4: Now select Goal Seek function and as per syntax, select the target value which we need to achieve. Here, that target is 90% accurate.

Code:

Sub Goal_Seek()

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

End Sub

Goal Seek function Example1-4

Step 5: Now, at last, select the range of that cell, where we need to see the required accuracy to be achieved. Here that cell is C6 with cell reference to Friday.

Code:

Sub Goal_Seek()

Range("C8").GoalSeek Goal:=90, ChangingCell:=Range("C6")

End Sub

VBA Goal Seek Example1-5

Step 6:  Now once done, we will compile the code and run it. We will see cell C6 has the Changing Cell value as 81. This means, if that employee has managed to achieve even 81% of accuracy on Friday, then he will easily be able to achieve the accuracy of 90%.

VBA Goal Seek Example 1-7

VBA Goal Seek – Example #2

In this example, we have data of Turn Around Time (TAT or time to complete the activity) of some employee of a team who performs the same task every day. 5 employees do the same activity from Monday to Friday. The target time is set for completing that activity is 50 Mins. And we need to find how much time each employee needs to take to maintain the average completion time as 50 Mins or less. For this, follow the below steps:

VBA Goal Seek Example 2-1

Step 1: In Module, write the subprocedure for VBA Goal Seek as shown below.

Code:

Sub Goal_Seek2()

End Sub

VBA Goal Seek Example2-2

Step 2: Define a variable by which we can count from where we need to start Goal Seek as Long.

Code:

Sub Goal_Seek2()

Dim A As Long

End Sub

VBA Goal Seek Example 2-3

Step 3: Now again define 2 more variables using DIM with data type Range. First will be used to track the current final average and second will be used to link the reference cell where we want the output.

Code:

Sub Goal_Seek2()

Dim A As Long
Dim FinalAvg As Range
Dim Reference As Range

End Sub

VBA Goal Seek Example 2-4

Step 4: Define another variable as Integer to set the Target we want to achieve.

Code:

Sub Goal_Seek2()

Dim A As Long
Dim FinalAvg As Range
Dim Reference As Range
Dim Target As Integer

End Sub

VBA Goal Seek Example 2-5

Step 5: Here our target is 50 Mins, so set the Target variable with this value.

Code:

Sub Goal_Seek2()

Dim A As Long
Dim FinalAvg As Range
Dim Reference As Range
Dim Target As Integer
Target = 50

End Sub

Target variable Example 2-6

Step 6: Open a For-Next loop as shown below.

Code:

Sub Goal_Seek2()

Dim A As Long
Dim FinalAvg As Range
Dim Reference As Range
Dim Target As Integer
Target = 50
For
Next A

End Sub

For-Next Example 2-7

Step 7: Now select the range of those cells which has the values. Here those rows are from 3 to 7 numbers.

Code:

Sub Goal_Seek2()

Dim A As Long
Dim FinalAvg As Range
Dim Reference As Range
Dim Target As Integer
Target = 50
For A = 3 To 7
Next A

End Sub

VBA Goal Seek Example2-8

Step 8: Set the final average where we want to get from. Here that cell is at Row 9.

Code:

Sub Goal_Seek2()

Dim A As Long
Dim FinalAvg As Range
Dim Reference As Range
Dim Target As Integer
Target = 50
For A = 3 To 7
Set FinalAvg = Cells(9, A)
Next A

End Sub

Final Average Example 2-9

Step 9: Similarly set the reference cell range, where we want to see the goal seek value to achieve the target and that cell Row is 7.

Code:

Sub Goal_Seek2()

Dim A As Long
Dim FinalAvg As Range
Dim Reference As Range
Dim Target As Integer
Target = 50
For A = 3 To 7
Set FinalAvg = Cells(9, A)
Set Reference = Cells(7, A)
Next A

End Sub

Reference Cell Range Example 2-10

Step 10: At last, apply the VBA Goal Seek with proper syntax as shown below.

Code:

Sub Goal_Seek2()

Dim A As Long
Dim FinalAvg As Range
Dim Reference As Range
Dim Target As Integer
Target = 50
For A = 3 To 7
Set FinalAvg = Cells(9, A)
Set Reference = Cells(7, A)
FinalAvg.GoalSeek Target, Reference
Next A

End Sub

VBA Goal Seek Example 2-10

Step 11: Now compile the complete code by pressing the F8 function key and run after that if no error is found.

We will see, at row 7 with the help of Goal Seek we will get time in minutes that each employee has to achieve on Friday to maintain the average of 50 mins or below.

VBA Goal Seek Example 2-12

This is how VBA Goal Seek works.

Pros of Excel VBA Goal Seek

  • VBA Goal Seek is quite helpful in tracking the performance in various ways.
  • This can also be used in many statistical work where we need to track and target the data limit which we want to achieve.
  • This tells the exact value the person needs to achieve to get the target.

Things to Remember

  • This can be used in Excel and VBA both.
  • Result cell with average or other function must always have the formula.
  • We can enter the target value in Goal in the Goal Seek syntax or else we can define this earlier separately as well.
  • We can change and update the target value as per our needs and requirement.
  • This task cannot be performed by recording the macros option available under the developer tab.
  • Once done, remember to save the excel file in macro enable format to retain the written code.

Recommended Article

This is a guide to VBA Goal Seek. Here we discuss how to use Goal Seek in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Collection
  2. VBA IF Statements
  3. VBA While Loop
  4. VBA Environ

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA Goal Seek Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW