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

### Like this:

Like Loading...

*Related*

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 ?

LikeLike

Thanks, this post helped me a lot … 🙂

LikeLiked by 1 person

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

This has changed my life, thank you

LikeLiked by 1 person

Thanks for this code… it was great

LikeLike

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

LikeLike

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.

LikeLike

Thank you I think I understand better now

LikeLike

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

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?

LikeLike

Jessica, see https://newtonexcelbach.wordpress.com/2013/01/30/goal-seek-macro-for-multiple-sheets/

LikeLike

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

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!

Regards

John Pollard

LikeLike

John – see https://newtonexcelbach.wordpress.com/2013/05/20/using-goal-seek-on-a-multi-column-range/

Any questions, please ask.

LikeLike

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

LikeLike

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

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)

Next

End Sub

LikeLike