Using Goal Seek on Multiple Cells

The Excel “goal seek” function is useful and powerful, but the procedure for using it on multiple cells is painfully slow.

The spreadsheet GSeek.xls provides a simple macro to allow Goal Seek to be automatically applied to a range of cells, arranged in either a column or row.  As usual,  it includes open source code for all sub-routines and functions.

The spreadsheet includes an example finding the first root of 20 quartic equations, compared with the analytic solutions using the User Defined Function Quartic().

GSeek Output

GSeek Output

This entry was posted in Excel, Maths, VBA and tagged , , , . Bookmark the permalink.

17 Responses to Using Goal Seek on Multiple Cells

  1. David says:

    it is excellent your publication, but you don not have examples more simples of how use a Macro into different rows for exapmle if my macro is:

    Sub Macro3()

    ‘ Macro3 Macro
    ‘ Macro grabada el 14/08/2009 por WinuE

    ‘ Acceso directo: CTRL+w

    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    End Sub

    I want to know if it is possible to make that this macro make the same for cells A6, A7, A8 …………………. etc.

    and I would like to know if the same code would work for goal seek ?


  2. Prashant says:

    Thanks, this post helped me a lot …🙂


  3. Pingback: 2010 in review | Newton Excel Bach, not (just) an Excel Blog

  4. Ed says:

    This has changed my life, thank you


  5. Abhinav Mathur says:

    Thanks for this code… it was great


  6. JosEliez says:

    Can you explain the following:

    NumEq = ARange.Rows.Count
    If NumEq = 1 Then
    NumEq = ARange.Columns.Count
    Orient = “H”
    Orient = “V”

    Why the NumEq equals the ARange.Rows and then it equals 1 and then ARange.Columns
    Also what is Orient and why is it referenving H and V


    • dougaj4 says:

      The input data is assumed to be arranged in a column (Orientation = Vertical), unless it is only 1 row, in which case it is assumed to be in a row (Orientation = Horizontal).

      If Orient = “V” then it works down the column:
      ChangingCell:=ARange.Cells(i, 1)

      or if Orient = “H” it works across the row:
      ChangingCell:=ARange.Cells(1, i)

      Hope that helps.


  7. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

  8. Jessica says:


    This code works great! I just want to know how to make it work for multiple tabs in a spreadsheet. I don’t remember too much about VBA from civil engineering school so I’d appreciate the dummy version. It worked for one tab, but I tried using it for tables on other tabs and failed miserably. I was thinking it was a name issue as it would always refer to the Taddr, Gval and Aaddr on the original sheet because they are absolute names. So I gave the cells on my other sheets different names and pasted them exactly where they would be in the code, but I got an error “Run time error ‘1004’:

    Method ‘Range’ of object’_Global’ failed

    Help? How would I apply this seperately to multiple tabs?


  9. Pingback: Goal Seek Macro for Multiple Sheets | Newton Excel Bach, not (just) an Excel Blog

  10. John Pollard says:

    Re.: Goal Seek on multiple cells:-

    There is not quite enough explanation for me – I am lost.
    I have formula, similar to, but a little more complicated than: z = 1/(sqrt(H^2 + (A-x)^2 + (A-y)^2).
    If I can understand the operstion for this situation, I should be able to solve my real problem.
    A is a fixed but chosen number.
    z is a fixed but chosen number.
    x ranges say from -10 to +10 in increments of 1.
    y ranges say from -10 to +10 in increments of 1.
    I would like to find the value of h at each coordinate (x,y) that produces the required constant specified value of z.
    And produce a spreadsheet of the values of h for each coordinate (x,y).
    Is this description succinct enough?
    From your description, I suspect your macro will do the trick.
    But I need detailed step by step help!

    John Pollard


      • uma kapur says:

        Hi……I need to run this goal seek formula for around 600 rows in my sheet ( for 500 different cells i need to run the same goal seek..)… there are blank rows on the worksheet which I need the macro to ignore….How do i do it without writing 500 lines… please help

        Simple macro is :

        Sub stk_opt()

        ‘ stk_opt Macro

        Range(“AQ2”).GoalSeek Goal:=0, ChangingCell:=Range(“AL2”)
        Range(“W2”).GoalSeek Goal:=0, ChangingCell:=Range(“R2”)
        Range(“AQ3”).GoalSeek Goal:=0, ChangingCell:=Range(“AL3”)
        Range(“W3”).GoalSeek Goal:=0, ChangingCell:=Range(“R3”)

        End Sub


  11. Pingback: Using Goal Seek on a multi-column range | Newton Excel Bach, not (just) an Excel Blog

  12. Nabil Amer says:

    Private Sub GoalSeek()
    Dim i As Long
    For i = 4 To 6 ‘you can here define the start row number and to the last row you want
    Range(“H” & i).GoalSeek Goal:=Range(“I” & i).Value, ChangingCell:=Range(“A” & i)
    End Sub


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s