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
Newton Excel Bach, not (just) an Excel Blog 
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