Excel Goal Seek VBA Code for a Whole Column

VBA & macros · Updated November 19, 2026

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

  1. Save a copy of the workbook before running the macro. VBA changes cannot be undone with Ctrl+Z.
  2. Save the working file as an Excel Macro-Enabled Workbook (.xlsm), so the VBA code can be retained.
  3. 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.
  4. Return to Excel and run GoalSeekDownColumn from Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
  5. 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 the Worksheets reference 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.