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%.
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 VBA RANGE object, later we can access GOAL SEEK option.
Below is the syntax of goal seek in VBA.
- 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
Following are the examples of goal seek in Excel VBA.
VBA Goal Seek – Example #1
Let’s take the example of examination average score only. Below is the anticipated score of 5 subjects from the completed exam.
First, we need to arrive on what is the average score from the completed 5 subjects. Apply AVERAGE function in B8 cell.
In this example our Goal is 90, 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.
Start the subprocedure in VBA module.
Sub Goal_Seek_Example1() End Sub
Now we need the result in B8 cell, so supply this range reference by using RANGE object.
Sub Goal_Seek_Example1() Range ("B8") End Sub
Now put a dot and enter “Goal Seek” option.
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.
Sub Goal_Seek_Example1() Range("B8").GoalSeek Goal:=90 End Sub
Next argument is “Changing Cell” for this we need to supply in which cell we need the new value to achieve the Goal.
Sub Goal_Seek_Example1() Range("B8").GoalSeek Goal:=90, ChangingCell:=Range("B7") End Sub
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.
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 example of finding the final examination score for more than one student.
Below are the anticipated scores of 5 subjects after the exam.
Since we are finding the goal for more than one student we need to use loops, below is the code for you.
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
This code will loop through all the students’ scores and arrive final examination score required to achieve the overall average of 90.
So we got the end result now as,
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 tool as well as VBA tool.
- Result cell should always contain a formula.
- We need to enter goal value and changing cell reference to the goal seek tool.
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 downloadable excel sheet. You can learn more from the following VBA articles –