## 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

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

### 15 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

Range(“A5″).Select
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. Ed says:

This has changed my life, thank you

4. Abhinav Mathur says:

Thanks for this code… it was great

5. JosEliez says:

Can you explain the following:

NumEq = ARange.Rows.Count
If NumEq = 1 Then
NumEq = ARange.Columns.Count
Orient = “H”
Else
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.

• JosEliez says:

Thank you I think I understand better now

6. Jessica says:

Hi,

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?

• dougaj4 says:
7. 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?