Goal Seek Analysis for Multiple Cells (Excel VBA)

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: drive.google.c...
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 ...
Refer this video - • Enable Developer tab i... if you are unable to find "Developer" tab in your excel.
Refer this video - • Adding Buttons In Exce... 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: www.bensound.com

[@hdtass]

Legend. So simple - have been mulling over how to do this for weeks and you show me in 3 lines of code in 2 minutes even though I have never coded ever. Thanks!

[@potentious1]

Just came across your page. Super simple, concise, and helpful. This is great, thank you for sharing your knowledge.

[@maqabayker]

Truly an engineering-hack! You really helped, man. You are Legend!
I tried to set it somehow differently to have inputs but then said myself to just use the basic functionality and get the results.

[@SALIS_LIGHT]

Great work. Simply and easily described each important step in the shortest time possible. Thank you very much indeed.

[@ricardocruz8252]

Thank you so much!! So simple and easy to make modifications. Appreciate you!

[@limarandos]

Hey man, thank you so much for taking the time to do this video.

[@ericwhittaker3570]

This video made me look smarter than I really am. Thanks!

[@cynthia7265]

Thank you so much for sharing, wish you have a great day.

[@steverich977]

Thanks for the video!

[@Dilume]

Thanks for the video and the file, that was really helpful

[@thanhlongtran4104]

Thank you very much. Its very helpful.

[@Greymatter57]

Thank you, it was very simple and nicely explained. It helped me in my project which had some deadlines. Can we do the same for rows? You example had columns from 5 to 13 assigned to variable j.

[@dwarkesh27jayram32]

Thank you really helpful.

[@joshf1234]

I literally love you

[@vaibhavgupte7921]

This works when your table is vertical. how to make it work when you table is horizontal. i.e. when i want the next j to next column and not a row

[@TheZaffi69]

Excellent video! really good!

[@matthewbrierley88]

Amazing. Legend. Thank you!

[@gametym8102]

Thank you very much sir

[@LumpineeSTD]

U are the King

[@alexandrusichin]

Masterpiece

[@Pauwel2324]

What if I want the goal seek value to be different for all lines?

[@03santuy73]

Thank you sir

[@kamwalebanda2979]

Hello am runing the code but still geting an error, can i whats app you

[@kunalvegada1405]

sir how to use for multiple cells.. my example if if i two columns one is department other is expenditure i want to change total of expenditure by varying all departments expenditure

[@dzmitryzhylinski8143]

Hey, is there the same solution for Google sheets?

[@gavilanmusic6457]

New to VBA- what is the importance of "j" in this?

[@raphaelaguiar502]

Thanks

[@faysalahmed1253]

where is the seek button code