In this video, you will see how to implement Goal Seek Analysis in Excel using Macro/VBA with a real life example that automates Goal seek for an entire column.

★★★★★

Video Companion spreadsheet that was referred in the video is available at: [ Ссылка ]

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 - [ Ссылка ]

Refer this video - [ Ссылка ] if you are unable to find "Developer" tab in your excel.

Refer this video - [ Ссылка ] 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:04 : Demo of Goal Seek Analysis Automation for Multiple Cells
00:46 : Open the Excel file and Enable content
01:06 : Creating the buttons (Reference to a different video)
01:38 : Enabling Developer tab in your Excel (Reference to a different video)
01:53 : The VBA CODE walk-through
02:18 : The RESET BUTTON logic in VBA
02:54 : The GOAL SEEK ANALYSIS logic in VBA

In this video, we look at how to automate Goal Seek Analysis for Multiple Cells in Excel for a single GOAL.

The code is as below...

Sub Goal_Seek_Range_SingleGoal()
Dim j As Integer
For j = 5 To 13
Cells(j, "F").GoalSeek Goal:=Cells(6, "H"), ChangingCell:=Cells(j, "E")
Next j
End Sub

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

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

This formula has 3 parts,

Part 1: Cells(j, "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 (j,"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(6, "H")
In this section, we are hard-coding the Goal Seek value. If you would like to have different goal values for different rows, this will be explained as part of a different video.

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(j, "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 (j,"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 j", the program increments the value of "j" and starts the next loop. This is continued till the value of "j" 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: [ Ссылка ]

свернуть