Excel Macro to Goal Seek for Multiple Cells (3 Variants)

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.

Dataset-Excel Macro Goal Seek Multiple Cells

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

Macro-1

➤ 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.

Outcome-Excel Macro Goal Seek Multiple Cells


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 Sub

For Command Button Reset Values:

Private Sub CommandButton2_Click()
 Range("E5:E13").Select
 Selection.ClearContents
End Sub

Macro-2

➤ 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).

Click-Excel Macro Goal Seek Multiple Cells

Step 3: Now, click on the Reset Values Command Button to reset previously calculated values.

Reset-Excel Macro Goal Seek Multiple Cells

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

Macro-3

➤ 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.

1st Input Box-Excel Macro Goal Seek Multiple Cells

Step 3: Again, assign a Set Value range (i.e., H5:H13) afterward click OK.

2nd Input Box

Step 4: At last, provide the about to Change Value range (i.e., E5:E13). Then click on OK.

3rd Input Box

🔄 After running the macro, you get a similar outcome depicted in the image below.

Final Outcome-Excel Macro Goal Seek Multiple Cells


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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo