Goal Seek VBA: Automate Excel What-If Analysis

In Excel VBA, use the GoalSeek method on a formula cell to make it reach a target by changing one input cell. For example, Range("B4").GoalSeek Goal:=12000, ChangingCell:=Range("B2") tells Excel to adjust B2 until the formula in B4 is 12,000.

What Goal Seek VBA does

Goal Seek is Excel’s one-variable what-if analysis tool. You provide a result you want, identify a cell that calculates that result, and select one input cell Excel is allowed to change. Excel then estimates a value for the input that makes the formula return the target.

VBA exposes this feature through the GoalSeek method of a worksheet range. The range that receives the method must contain a formula; the ChangingCell argument identifies the input to adjust. The method returns True if Excel reports that it found a solution and False if it did not.

This is useful when a workbook needs to repeat a familiar calculation: a macro can set up or refresh assumptions, run Goal Seek, and report whether it succeeded. It does not turn Goal Seek into a general-purpose optimizer. It still changes just one cell, and the result depends on the formula and starting values.

How Goal Seek works in Excel

On Excel for Microsoft 365 on Windows, the manual command is Data > What-If Analysis > Goal Seek. In the dialog, enter the formula cell in “Set cell,” the desired result in “To value,” and the adjustable input in “By changing cell.” Click OK to run it. The VBA method performs the same kind of calculation without requiring the dialog.

Conceptually, Excel varies the input, recalculates formulas that depend on it, and checks how close the result is to the target. This is an iterative numerical calculation, not a rearrangement of the formula into an exact algebraic answer. A result may therefore be approximate, and a complicated or discontinuous formula may make a solution difficult to find.

Set cell: a single formula cell whose calculated value should meet the target.
To value: the target number, supplied in VBA as the Goal argument.
By changing cell: one input cell, supplied as ChangingCell.

The target does not have to be typed as a hard-coded number in a macro; it can be read from a worksheet cell and passed as a variable. However, Goal Seek’s changing cell is still one cell, not a range of assumptions.

A concrete VBA example

Suppose a worksheet named Model has the number of units in B3, the unit price in B2, and total revenue in B4. With units in B3 set to 400 and the price initially set to 25, the formula in B4 is =B2*B3, so revenue is 10,000. To find the price that produces revenue of 12,000, Goal Seek should change B2 and leave B3 alone. The expected price is 30 because 30 multiplied by 400 equals 12,000.

With that layout and the formula in B4, this macro runs the calculation and displays whether Excel reports success:

Sub FindPriceForRevenue()
    Dim ws As Worksheet
    Dim reached As Boolean

    ' Use the worksheet that contains the model.
    Set ws = ThisWorkbook.Worksheets("Model")

    ' Change the price in B2 until the revenue formula in B4 reaches 12000.
    reached = ws.Range("B4").GoalSeek( _
        Goal:=12000, _
        ChangingCell:=ws.Range("B2"))

    If reached Then
        MsgBox "Goal reached. Price: " & ws.Range("B2").Value
    Else
        MsgBox "Goal Seek did not report a solution. Check the model."
    End If
End Sub

Here the expected result is 30 in B2, and B4 should recalculate to 12,000. The code assumes the worksheet is named exactly Model, B4 already contains the formula, and B2 is a suitable numeric input. If your workbook uses different cells or a different sheet name, update those references before running the macro.

How to add and run the macro

In desktop Excel for Windows, save a copy of the workbook first, then save the working file as an Excel Macro-Enabled Workbook (.xlsm). Macros can change cell values, and their changes cannot be reversed with Ctrl+Z. A backup gives you a way to restore the original model if a macro changes the wrong cell or produces an unwanted result.

Open the VBA editor with Alt+F11. On Mac, Option+F11 may open it; some keyboards require Fn+Option+F11.
In the editor, choose Insert > Module, then paste the procedure into the module.
Change the sheet name, cell addresses, and target so they match your workbook. Confirm that the set cell contains a formula.
Return to Excel and run it from Developer > Macros by selecting the procedure and choosing Run. Alt+F8 opens the Macro dialog on Windows; Option+F8 is the corresponding shortcut on many Mac keyboards.

If the Developer tab is hidden in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. On Mac, use Excel > Preferences > Ribbon & Toolbar and enable Developer. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros; use the desktop app for this procedure.

Check the result instead of trusting the message

A True return value is useful, but it is not a substitute for validating the workbook. Check the changed input, recalculate or inspect the formula cell, and compare its value with the requested target. In the example, confirm that B2 is near 30 and B4 is 12,000. For targets that cannot be represented exactly, decide what difference is acceptable for the purpose of the model.

For a repeatable macro, consider recording the original input value before running Goal Seek, then writing the final input and result to a clearly labeled output area or log. That makes it easier to review what the procedure changed. If a failed attempt should not leave a changed input behind, save its original value and restore it when the method returns False. Do not restore it automatically after success unless that is specifically the intended workflow.

Also confirm that the input cell is genuinely the variable you want to solve for. A macro can run successfully while changing an unintended assumption if its cell reference is wrong. Clear labels, named ranges, and a visible result check reduce that risk.

Common problems and how to diagnose them

The set cell is not a formula. Goal Seek needs a formula whose result responds to the input. Check the formula cell and its dependencies.
The changing cell is on another worksheet. Goal Seek expects the input cell to be on the same worksheet as the set cell. If the model is spread across sheets, consider placing a linked input or result on the model sheet, or redesigning the calculation so the required cells are together.
The result does not move when the input changes. The formula may not depend on the changing cell, or calculation settings and dependencies may prevent the expected update. Trace the formula and verify that changing the input manually affects the result.
The method returns False or produces an implausible value. Try a sensible starting value, check for errors such as #VALUE! or #DIV/0!, and verify that the target is mathematically attainable. Goal Seek does not enforce business rules such as a nonnegative price or a maximum quantity.
The macro cannot find the worksheet. The name in Worksheets("Model") must match the worksheet tab exactly. A renamed or misspelled sheet causes a VBA error rather than a valid Goal Seek result.
The macro is blocked. Confirm the file is an .xlsm workbook and that the macro is allowed by your organization’s security settings. Do not enable macros in files from untrusted sources.

When a model has multiple possible solutions, a different starting input can lead to a different result. Test the answer against the model’s real-world constraints instead of assuming that the first numerical solution is the only meaningful one.

Goal Seek versus Solver and formulas

Use Goal Seek when one output must reach a target by changing one input and there are no additional constraints to enforce. It is quick to call in VBA and is often enough for tasks such as finding a price for a target revenue, an attendance level for a target total, or an input that makes a formula equal a specified value.

Use Excel Solver when the problem needs multiple changing cells, explicit limits, or an objective to maximize or minimize. Solver is a separate Excel add-in, and automating it uses a different interface from the Range.GoalSeek method. For a simple one-input calculation, adding Solver may be unnecessary; for a constrained model, Goal Seek’s one-cell scope may be insufficient.

If the relationship is simple and can be solved directly, a normal formula may be clearer and easier to audit than an iterative macro. For example, if revenue equals price times units and units are fixed, price is revenue divided by units. Goal Seek is more helpful when the formula is complicated, contains several dependent calculations, or is inconvenient to rearrange.

Safety and compatibility notes

VBA macros can edit workbook contents, so keep a separate copy before testing and review the macro’s sheet and cell references. Ctrl+Z cannot undo changes made by a macro. Use a descriptive procedure name and avoid running code from an unknown workbook. In managed workplaces, follow the organization’s macro policy rather than changing security settings to bypass it.

Goal Seek is available in desktop Excel for Windows and Mac, though ribbon placement and keyboard behavior can differ by platform. VBA itself is not available to run in Excel for the web. If a workbook must work entirely in a browser, use a worksheet formula or another web-compatible approach instead of relying on this macro.

Frequently Asked Questions

What is the VBA syntax for Goal Seek?
Call GoalSeek on the formula range, providing a target with Goal:= and the input cell with ChangingCell:=. For example, Range("B4").GoalSeek Goal:=12000, ChangingCell:=Range("B2") targets 12,000 in B4 by changing B2.

Does Goal Seek VBA work with more than one changing cell?
No. Goal Seek adjusts one changing cell for one formula result. For multiple adjustable cells or constraints, consider Solver or another suitable model-solving approach.

Why does Goal Seek return False?
It can fail to report a solution if the target is not reachable, the formula is not responsive to the input, or the calculation is difficult to solve from the current starting value. Check the formula, input, target, and starting point.

Can I run Goal Seek VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro.

Can Goal Seek enforce a minimum or maximum value?
No. Goal Seek does not apply bounds or other constraints to its changing cell. It may return a value outside the range you consider valid; use Solver or validate the result separately when limits matter.

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.