Maruf Islam
In What-If Analysis, Goal Seek Analysis is one of the tools to perform value impact depending on outcomes. Excel Macro Goal Seek multiple cells is one of the many incidents where we need to get cell values considering a fixed result or outcome.
Let’s say, we have calculated cell values considering working hour completion by workers. Now, we want to execute a calculation to find what-if we want an overall 50% progress, and what will be the required working hours.
In this article, we discuss multiple variants of macros to Goal Seek multiple cells.
Download Excel Workbook
Goal Seek and Its Function in Excel
Goal Seek is a tool under What-If Analysis that calculates values depending on set goals. If users want to back-calculate any formula outcome, Goal Seek does the job. What Goal Seek really does is this – it takes a value as a “Need to Achieve” one and then returns cell values to fit in the formulas. It requires 3 parameters to be assigned. They are,
1. A range that is about to hold the required/set value.
2. Required/Set value.
3. A range to hold changed values to fulfill the Set value.
3 Variants of Excel Macro to Goal Seek for Multiple Cells
Before inserting Macros use ALT+F11 or go to the Developer tab > Visual Basic to open Microsoft Visual Basic window. Afterward, execute Insert > Module or right-click on any Worksheet > select Module to insert a Module.
Method 1: Using Macro to Change Goal Seek for Multiple Cells
To change the Goal Seek value for the multiple cells at one go you can follow the following macro.
Step 1: Paste the following macro in the Module.
Sub GoalSeekMultipleCells()
For p = 5 To 13
Cells(p, "F").GoalSeek Goal:=0.5, ChangingCell:=Cells(p, "E")
Next p
End Sub
➤ in the code,
1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.
2 – execute the VBA FOR function for a specific number of rows (i.e., rows 5 to 13) to assign the “Set Value” column (i.e., Column F), “Set Value” (i.e., 0.5), and about to change cells (i.e., Column E).
Step 2: Press F5 to run the macro. Return to the Worksheet, you see for all Phases, Project Progress is set at 50% and Goal Seek calculates the required Worked Hours as desired.
Method 2: Macro of Goal Seek with Reset Option
In this section, we’ll see another variant of the macro where you will get a reset option to make a fresh start when you prefer. Let’s start.
Step 1: Assign the below macro to Macro Command Buttons (i.e., Goal Seek and Reset Values). Follow This Link to insert Command Button for Macro.
For Command Button Seek Goal:
Private Sub CommandButton1_Click()
Dim m As Integer
For m = 5 To 13
Cells(m, "F").GoalSeek Goal:=Cells(5, "H"), ChangingCell:=Cells(m, "E")
Next m
End SubFor Command Button Reset Values:
Private Sub CommandButton2_Click()
Range("E5:E13").Select
Selection.ClearContents
End Sub
➤ From the above image, in the sections,
1 – begin the macro code declaring the VBA Macro Code’s Sub name for Command Button 1 (i.e., Goal Seek).
2 – declare the variable, also assign desired “Set Value” range (i.e., Column F), “Set Value” (i.e., H5), and about to change values (i.e., Column E). At last, execute the VBA FOR loop.
3 – declare the Sub name for Command Button 2 (i.e., Reset Values).
4 – clear the range (i.e., E5:E13) to prepare the cells for further calculation.
Step 2: Click on the Seek Goal button to apply the H5 value in F Columns. After that Goal Seek returns the required values in the E column (i.e., rows 5 to 13).
Step 3: Now, click on the Reset Values Command Button to reset previously calculated values.
You can repeat the calculations or further modify the macro or Command Buttons.
Method 3: Using Macro Input Box to Assign Values for Goal Seek Calculation
You may need to provide your own input while running an operation. This section may help you to do so. Let’s see the code and the usage.
Step 1: Type the following macro in a Visual Basic Module.
Sub GoalSeekwithPrompt()
Dim InputRng As Range, SetVal As Range, OutputRng As Range
Dim p As Long
With Application
Set InputRng = .InputBox(Title:="Select a Desired Range", _
prompt:="Select desired range containing ""Set Value"" in cells", Default:=Range("F5:F13").Address, Type:=8)
Set SetVal = .InputBox(Title:="Select a Desired Range", _
prompt:="Select desired range containing ""Set Value"" change into", Default:=Range("H5:H13").Address, Type:=8)
Set OutputRng = .InputBox(Title:="Select a Desired Range", _
prompt:="Select desired range you want the values to be changed", Default:=Range("E5:E13").Address, Type:=8)
End With
For p = 1 To InputRng.Rows.Count
InputRng.Cells(p).GoalSeek Goal:=SetVal.Cells(p).Value, ChangingCell:=OutputRng.Cells(p)
Next p
End Sub
➤ The above image has sections,
1 – take forward the macro by setting the Sub name and assigning variable types.
2 – execute the Object using VBA WITH. Also, assing variables using the VBA InputBox function.
3 – execute the VBA FOR loop for rows (i.e., 5 to 13). It also assigns the Set Value range, Set Value, and about to Change Values.
Step 2: To run the macro press F5. The macro fetches an Input Box named Select a Desired Range. Assign the outcome range (i.e., F5:F13). Then click OK.
Step 3: Again, assign a Set Value range (i.e., H5:H13) afterward click OK.
Step 4: At last, provide the about to Change Value range (i.e., E5:E13). Then click on OK.
🔄 After running the macro, you get a similar outcome depicted in the image below.
Conclusion
In this article, we demonstrate multiple variants of macros to Excel Goal Seek multiple cells. Use any of the described macros to calculate multiple values. Hope these mentioned methods help you to accomplish your goals. Comment, if you have further inquiries or have anything to add.
