To run Goal Seek down a column, loop through the populated rows and call Range.GoalSeek once per row: each call needs one formula cell, one target value, and one changing cell. Excel cannot Goal Seek an entire column in a single operation, but the VBA example below applies the calculation row by row and records whether each attempt succeeded.
What Goal Seek can solve
Goal Seek is Excel’s one-variable, what-if analysis tool. It works backward from a desired result: you specify a formula cell, the value you want that formula to return, and one input cell Excel may change. In VBA, the method is called on the formula cell, with the target and changing cell supplied as arguments.
For example, if a row calculates a total as a quantity multiplied by a unit value, Goal Seek can find the unit value that makes the total 100. It changes only one input at a time. It is not a general-purpose optimizer for several changing inputs, constraints, or a whole range of results at once.
Set up a row-by-row model
The macro below assumes a worksheet named Model with this layout:
- Column A contains a coefficient or other row identifier. A populated cell marks a row to process.
- Column B contains the changing value Goal Seek is allowed to adjust.
- Column C contains that row’s formula result.
- Column D is reserved for the macro’s status message.
- Cell E1 contains the numeric target shared by all rows, such as 100.
With the coefficient in A2 and the changing input in B2, the formula in C2 is =A2*B2. Copy the corresponding formula down column C so that each row refers to its own A and B cells. If A2 is 2 and B2 starts at 10, C2 returns 20. Setting E1 to 100 means Goal Seek should change B2 to 50, because 2 multiplied by 50 equals 100.
Every row should have its own formula and changing cell. A formula that points to another row, or a changing cell that is also used by other rows, can make the results interact instead of solving independently.
VBA code to Goal Seek each populated row
Put this macro in a standard VBA module in the workbook containing the Model sheet. It finds the last populated row in column A, checks that the target and row inputs are usable, and writes a result in column D. Change the worksheet name or column references if your layout differs.
Option Explicit
Sub GoalSeekDownColumn()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Dim goal As Double
Dim targetCell As Range
Dim changingCell As Range
Dim reached As Boolean
Set ws = ThisWorkbook.Worksheets("Model")
' Read the shared target from E1.
If IsError(ws.Range("E1").Value2) Then
MsgBox "Cell E1 must contain a numeric target."
Exit Sub
End If
If Not IsNumeric(ws.Range("E1").Value2) Then
MsgBox "Cell E1 must contain a numeric target."
Exit Sub
End If
goal = CDbl(ws.Range("E1").Value2)
' Use column A to identify the last row to process.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
Set targetCell = ws.Cells(r, "C")
Set changingCell = ws.Cells(r, "B")
If Len(ws.Cells(r, "A").Value2) = 0 Then
ws.Cells(r, "D").Value = "Skipped: blank key"
ElseIf Not targetCell.HasFormula Then
ws.Cells(r, "D").Value = "Skipped: no formula"
ElseIf IsError(targetCell.Value2) Then
ws.Cells(r, "D").Value = "Skipped: formula error"
ElseIf Not IsNumeric(targetCell.Value2) Then
ws.Cells(r, "D").Value = "Skipped: result is not numeric"
ElseIf IsError(changingCell.Value2) Then
ws.Cells(r, "D").Value = "Skipped: input error"
ElseIf Not IsNumeric(changingCell.Value2) Then
ws.Cells(r, "D").Value = "Skipped: input is not numeric"
Else
reached = False
' Capture a row-level error rather than stopping the whole loop.
On Error Resume Next
reached = targetCell.GoalSeek( _
Goal:=goal, ChangingCell:=changingCell)
If Err.Number <> 0 Then
ws.Cells(r, "D").Value = "Error: " & Err.Description
Err.Clear
ElseIf reached Then
ws.Cells(r, "D").Value = "Reached"
Else
ws.Cells(r, "D").Value = "Goal not reached"
End If
On Error GoTo 0
End If
Next r
End Sub
The escaped symbols <> and & in the displayed code represent VBA’s <> comparison operator and & text-concatenation operator. If copying the code from an HTML-rendered page, use the actual VBA characters: <> should be typed as the less-than sign followed by the greater-than sign, and & as the ampersand.
Run the macro safely
- Save a copy of the workbook before running the macro. VBA changes cannot be undone with Ctrl+Z.
- Save the working file as an Excel Macro-Enabled Workbook (
.xlsm), so the VBA code can be retained. - On Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > Module, paste the code, and check that the worksheet tab is named
Model. - Return to Excel and run
GoalSeekDownColumnfrom Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. - Review column D, then inspect the changed values in column B and calculated results in column C.
Only enable macros in workbooks you trust. The macro changes input cells in the active workbook; saving a backup first gives you a straightforward way to restore the original values if the results are not what you intended.
Why the loop stops at the last used row
“A whole column” usually means all the data rows in a column, not every cell Excel can hold. A worksheet has 1,048,576 rows, so looping through every possible row wastes time when a model contains only a few hundred records. This code finds the final nonblank cell in column A and processes rows 2 through that row. Row 1 is treated as a header.
Column A is the macro’s guide to the data range. If your key or identifier is in a different column, change both occurrences of "A" in the last-row and blank-row checks. Choose a column that is consistently filled for every record you want included. If that column has gaps, the blank rows are marked as skipped, while later rows are still considered.
The macro also writes to column D and reads the shared goal from E1. If those cells contain important data in your workbook, change the status and target locations in the code before running it.
Limits and how to interpret the result
Goal Seek uses an iterative numerical calculation; it is not a promise that every row has a solution. The Boolean returned by GoalSeek indicates whether Excel found the requested target. A “Goal not reached” status means that call did not report success. A row-level error message points to a calculation or reference problem that prevented the call from completing normally.
Even when the target is reached, check whether the answer is meaningful for your model. A formula can have more than one possible input that produces the same result, and a numerical search may find a solution that is mathematically valid but outside your practical limits. Goal Seek does not enforce bounds such as “input must be between 0 and 1,000.” Review the formula, the adjusted input, and the resulting value rather than treating a status message as business approval.
Rows are solved one after another. If formulas in later rows depend on cells changed by earlier Goal Seek calls, the model is not independent by row, and the final values may depend on the order of calculation. For genuinely independent rows, each formula should use only its own row’s input and stable references to shared assumptions.
Common problems and fixes
- The macro reports “no formula.” Goal Seek needs a formula in column C, not a typed result. Check that formulas have been copied into every data row.
- The input does not change. Column B must be a value cell that Excel can alter, not a formula cell or a protected cell. Check worksheet protection and the cell’s contents.
- Some rows are skipped. The code intentionally skips blank keys, formula errors, nonnumeric results, and nonnumeric inputs. Correct the row or adjust the validation rules if a different input format is appropriate.
- The result is not the expected one. Verify that the formula in column C depends on the corresponding B cell and that E1 contains the intended target. Also check whether the model uses rounded values, limits, or references to other rows.
- The macro stops because a sheet name cannot be found. Rename the sheet tab to
Model, or replace that name in theWorksheetsreference with the exact tab name.
For a large model, test a few representative rows first. You can also filter the data or temporarily limit the loop’s final row while checking the setup, then restore the intended range.
Manual Goal Seek, VBA, and alternatives
For one row, the built-in command is often simpler: select the formula cell, then choose Data > What-If Analysis > Goal Seek. Enter the desired value, identify the changing input cell, and start the calculation. This is useful for an occasional check, but repeating the dialog for hundreds of rows is tedious; VBA automates the repeated calls while using the same one-variable approach.
For a problem with multiple changing cells or constraints, Solver may be a better fit. Solver is an Excel add-in and may need to be enabled; it solves a different class of optimization problem and should not be treated as a faster version of row-by-row Goal Seek. If the relationship can be rearranged algebraically, a direct formula may be clearer and faster than iterative solving. For instance, if the result is coefficient multiplied by input and the coefficient is nonzero, the input for a target can be calculated directly as target divided by coefficient. That shortcut is only appropriate when it matches the actual model and its assumptions.
Windows, Mac, and Excel for the web
This example is intended for desktop Excel for Microsoft 365 on Windows. Excel for Microsoft 365 for Mac supports VBA macros, though the exact way to open the Visual Basic Editor and manage macro settings can differ by Mac version and keyboard configuration; use the Developer tab if available. The Windows Alt+F11 shortcut is not a reliable Mac instruction.
Excel for the web does not run VBA macros. Open the workbook in the desktop app to execute this code. If the file is stored in a cloud location, save changes after running it so the new input values and status messages are retained.
Frequently Asked Questions
Can Excel Goal Seek work on a whole column at once?
No. A Goal Seek operation uses one formula cell and one changing cell. To apply the same process to many rows, VBA loops through those pairs and calls Goal Seek separately for each row.
Can Goal Seek change more than one cell?
No. Goal Seek changes one input cell for a target formula. If your model needs multiple adjustable inputs or constraints, consider Solver or an appropriate direct calculation.
Why does Goal Seek say it cannot find a solution?
The formula may not reach the requested target by changing the selected input, or the numerical calculation may not converge to it. Check the formula and starting value, and confirm that the target is possible for the model.
Can I use this VBA code in Excel for the web?
No. Excel for the web does not execute VBA macros. Run the workbook in desktop Excel for Windows or Mac instead.
Will Ctrl+Z undo the macro?
No. Excel clears its normal undo history when a VBA macro changes the workbook. Save a backup copy before running the code so you can restore the original data if needed.
Related
- Excel Custom Print Area: Set, Adjust, and Troubleshoot
- Excel Continually Not Responding? Causes and Fixes
- Excel Named Range to Table: What Works and What Doesn’t
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Custom Order: Code Examples and Common Fixes in ExcelTo sort Excel rows in a chosen sequence with VBA, add a custom sort field and give it…
- Excel VBA: Count Visible Rows After AutoFilterTo count rows still visible after an AutoFilter, loop through the filter’s data rows and count those whose…
- Dim ws As Worksheet in VBA: Meaning and UseDim ws As Worksheet declares a variable named ws that can refer to an Excel worksheet, but it…
- Excel VBA String Comparison: Rules, Methods, and ExamplesIn Excel VBA, compare strings with StrComp when you want to choose case sensitivity explicitly: use StrComp(firstText, secondText,…
- VBA Array of Numbers: How to Declare and Work With OneIn VBA, an array of numbers stores multiple numeric values under one variable name; declare one with a…
- VBA Sort Data: Methods, Examples, and Common FixesIn Excel VBA, sort data with the worksheet’s Sort object: define the range, add one or more sort…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.