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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Read More: How to Use Goal Seek in Excel


Excel Macro to Goal Seek for Multiple Cells: 3 Variants

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

Read More: How to Use Goal Seek to Find an Input Value


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

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, assign 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


Download Excel Workbook


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


<< Go Back to Goal Seek in Excel | What-If Analysis in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo