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:

Below is the syntax of VBA Goal Seek.

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.

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.

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.

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

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

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

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

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 #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:

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

Code:

Sub Goal_Seek2()

End Sub

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

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

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

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

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

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

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

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

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

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.

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