Goal Seek for a Range with Multiple Goals (Excel VBA)

In this video, we will look at how to automate Goal Seek Analysis for a range of cells while setting Multiple Goals for each row. We look at a real life example where we try to figure out how much marks each student need to obtain in their external exams in order to reach the final individual goal preset for each student.

If you are starting with Goal Seek and have no idea on what goal seek analysis is, I recommend you to watch this video first - https://www.youtube.com/watch?v=jhT2yAWTSx4

You can copy the VBA code directly from the below link or from description below: https://gist.github.com/excelcrafters/74b0ad8f0f8274f8e2bb7eba68f5ec79

The companion excel referred in this video is available at:
https://drive.google.com/open?id=1FSIDrRkYQV7sW_Eo8NDeDMPlNwc88krC

Refer this video - https://www.youtube.com/watch?v=HL5qk0rlWtw if you are unable to find "Developer" tab in your excel.

Refer this video - https://www.youtube.com/watch?v=V3lfzYdm0j0 to see how you can create a button and link it to a Macro.

Index for this video is shown below. Feel free to move to the respective sections by clicking on the timestamp!

00:05 - Demo of Goal Seek Analysis Automation for Multiple Cells with multiple goals
02:11 - Looking at the Code & the goal seek formula
03:12 - The loop & iteration

The code is as below
★★★★★
Sub Goal_Seek_Range_MultipleGoal()
'Defining variable k
Dim k As Integer
'We are looping through each row in our table
For k = 5 To 13
'Below is how we replicate the Goal Seek functionality via VBA
Cells(k, "F").GoalSeek Goal:=Cells(k, "H"), ChangingCell:=Cells(k, "E")
'Go to next iteration
Next k
End Sub
★★★★★

Explanation of the code

In the code, we loop for all the rows where Goal Seek needs to be implemented... This is what the "For k = 5 To 13" does.

Then, we use the below formula to implement Goal Seek Analysis...
Cells(k, "F").GoalSeek Goal:=Cells(k, "H"), ChangingCell:=Cells(k, "E")

This formula has 3 parts,

Part 1: Cells(k, "F").GoalSeek
In this section, we are defining the cells that needs to be changed to the Goal Value... Since there are multiple cells, we need to make this cell value dynamic. This is the reason we give the value as Cells (k,"F"). So, in the first iteration, the value of this part of the code will be F5. In the next iteration, F6 and so on.

Part 2: Goal:=Cells(k, "H")
Since the Goal for each row is defined in Col H, we loop through each goal value based on this dynamic cell reference. So, in the first iteration, the value of this part of the code will be H5. In the next iteration, H6 and so on.

In this case, the cell H6 holds the hardcoded Goal value. Hence, we are giving the equivalent expression Cells(6, "H") here.

Part 3: ChangingCell:=Cells(k, "E")
This section of the code defines which cell needs to be changed in order to reach the defined Goal. As done before... since there are multiple cells, we need to make this cell value dynamic. This is the reason we give the value as Cells (k,"E"). So, in the first iteration, the value of this part of the code will be E5. In the next iteration, E6 and so on.

When the processing reaches "Next k", the program increments the value of "k" and starts the next loop. This is continued till the value of "k" becomes 13.

In the excel sheet, the Seek Button is assigned the above macro. So, when you click it, it runs the code and fills the value in Col E.

★★★★★

#goalseekvba #learn365club

Music: https://www.bensound.com

Видео Goal Seek for a Range with Multiple Goals (Excel VBA) канала Internetter