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


Method 1 – Using Macro to Change Goal Seek for Multiple Cells

Use ALT+F11 or go to the Developer tab and select Visual Basic to open Microsoft Visual Basic window. A

Click on Insert and then select Module or right-click on any Worksheet to select the Module option.

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



  1. Starts the macro procedure by declaring the Sub name. You can assign any name to the code.
  2. Executes 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. In the Worksheet, all Phases, Project Progress is set to 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


Step 1: Paste the following macro in the Module.

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()
End Sub



  1. Starts the macro code, declaring the VBA Macro Code’s Sub name for Command Button 1 (i.e., Goal Seek).
  2. Declares the variable, also assigns the desired “Set Value” range (i.e., Column F), “Set Value” (i.e., H5), and change values (i.e., Column E). Executes the VBA FOR loop.
  3. Declares the Sub name for Command Button 2 (i.e., Reset Values).
  4. Clears 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 Column F.

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:

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 as required.

Method 3 – Using Macro Input Box to Assign Values for Goal Seek Calculation

Step 1: Enter the following macro in a 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



  1. Sets the Sub name and assigns variable types.
  2. Executes the Object using VBA WITH, and assigns 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 opens an Input Box named Select a Desired Range.
  • Assign the outcome range F5:F13.
  • Click OK.

1st Input Box-Excel Macro Goal Seek Multiple Cells

Step 3:

  • Assign a Set Value range  H5:H13.
  • Click OK.

2nd Input Box

Step 4:

  • Enter the Change Value range E5:E13.
  • Click on OK.

3rd Input Box

The macro will return the below results.

Final Outcome-Excel Macro Goal Seek Multiple Cells

Download Excel Workbook


Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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