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 have targeted an average score of 90% from six available subjects. As of now, you have completed 5 exams. You 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%.

One can do this by using GOAL SEEK in excelUsing GOAL SEEK In ExcelThe Goal Seek in excel is a “what-if-analysis” tool that calculates the value of the input cell (variable) with respect to the desired outcome. In other words, the tool helps answer the question, “what should be the value of the input in order to attain the given output?” read more worksheet and VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. But, first, let us see how it works with VBA.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Goal Seek (wallstreetmojo.com)

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 using the VBA RANGEUsing The VBA RANGERange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more object. Later, we can access the GOAL SEEK option.

Below is the syntax of Goal Seek in VBA.

  • Range(): In this, we need to supply the cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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 examples of Goal Seek in Excel VBA.

VBA Goal Seek – Example #1

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

First, we must determine the average score from the completed 5 subjects. Then, Apply the AVERAGE functionApply The AVERAGE FunctionThe AVERAGE function in Excel gives the arithmetic mean of the supplied set of numeric values. This formula is categorized as a Statistical Function. The average formula is =AVERAGE(read more in the B8 cell.

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.

Start the Sub procedure in the VBA class moduleVBA Class ModuleUsers have the ability to construct their own VBA Objects in VBA Class Modules. The objects created in this module can be used in any VBA project.read more.

Code:

Sub Goal_Seek_Example1()

End Sub

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

Sub Goal_Seek_Example1()

Range ("B8")

End Sub

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

The first argument is the “Goal” for this. Next, we must 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

The next argument is “Changing Cell” for this, we need to supply the new value in which cell we need 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., the B7 cell.

Let us run the code to see what needs to be done in the final subject to achieve the overall average percentage of 90.

So, 95 has to be scored in the final subject 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 for 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 at the final examination score required to achieve the overall average of 90.

So, we got the 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 Students B and C. They need to score 104 each in the final examination, which is impossible.

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 and the VBA tool.The resulting cell should always contain a formula.We need to enter the goal value and change the cell reference to the Goal Seek tool.

This article 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 ScreenVBA With StatementUDF in Excel VBA