Youtor
Goal Seek for a Range with Multiple Goals Excel VBA
>> YOUR LINK HERE: ___ http://youtube.com/watch?v=3v9lVWh-OqE
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 - • Goal Seek Analysis in Excel (Simple E... • You can copy the VBA code directly from the below link or from description below: https://gist.github.com/excelcrafters... • The companion excel referred in this video is available at: • https://drive.google.com/open?id=1FSI... • Refer this video - • Enable Developer tab in Excel Ribbon ... if you are unable to find Developer tab in your excel. • Refer this video - • Adding Buttons In Excel (2 ways) 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
#############################
