Using Goal Seek as a function in a formula

[nqueen]

Hi,

I have an excel sheet in which there are data. For a specific purpose, I need to make the target cell to 0 while changing another value. I used the goal seek and this works fine. The problem I have is that I need to use the Goal Seek for almost all cells in an entire row. And I need to do it periodically.

I would like to know if there is a way to call that Goal Seek as a function in a forumla? instaead of doing it manually.

Thanks in advance for your help!

 

[mintjulep]

My brief attempt to use goalseek within a user defined function was not successful, but I suspect that it could be done.

It is pretty easy to write a macro to setup goalseek.

[codeSub Macro1()

Range("C1").GoalSeek goal:=5, changingcell:=Range("A1")
End Sub[/code]

 

[nqueen]

I know I ve thought abaout macros but my colleague is insisting on using goal seek function(invoking it) in a forumla. Does anyone knows how to do it?

 

[GlennUK]

Hi nqueen,

when you say that your colleague is insisting on using goal seek, does he/she realize that functions can only return values to the invoking cell, and not cause changes to other cells. Using a Sub would work ... but then again your colleague may not like that.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[nqueen]

Hi Glenn,
yes we realize that. The thing is what he wants is to use a formula in every cell and call the goal seek function in it so that it is done automatically and not having to use the goal seek manually every time for every cell every time. Is there a function that calls the goal seek through the forumla bar in excel?

 

[GlennUK]

The goalseek method ( not function ), changes the values of cells, so cannot be part of a function. You need to think about calling the GoalSeek method from a VBA routine, which can be done for many cells in a loop if necessary.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[nqueen]

so you are telling me that the only way to automate the goal seek method is to use a macro right?

 

[nqueen]

then is there another way to do the same thing than goal seek using another function that can be invoked from the formula bar?

 

[MeGustaXL]

Hi nqueen,

Have a look at the spreadsheet "GS Demo.zip" on Stephen Bullen's page:

http://www.oaltd.co.uk/Excel/SBXLPage.asp

It uses circular references in formulas to replicate the Goal Seek effect.



Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[nqueen]

Thanks Chris for the link it s quite useful. But I decided to build the macro, show it to my colleague and see what he thinks. Now my question pertains the VBA. Basically I started from here and it works but only for a specific cell.
Range("AX92").GoalSeek goal:=0, changingcell:=Range("AU92")


Now I need to do the loop. What I don t know is how to make the range be a variable so that I can increment it to let it know that it should look at the next cell's value ? (because I want to loop through all cells and call the gaoalseek function for each).


Does anyone have any idea?

 

[nqueen]

oh I forgot to add, the column does not change but the row does. e.g. next cell will be AY92 not AX93

thanks in advance!

 

[GlennUK]

It's very each to loop through cells, but I'm not sure you'd want to loop through all cells?

because I want to loop through all cells and call the gaoalseek function for each

Here are a couple of methods ( not tested ):

For Each c in Range("AX92:BC92")
   c.GoalSeek goal:=0, changingcell:=c.Offset(0,-3)
Next

For i = 50 to 100
   Cells(92,i).GoalSeek goal:=0, changingcell:=Cells(92,i-3)
Next

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[nqueen]

You re right Glenn , what I meant was I would have to go through many cells. Thanks for the loop, I made slight changes and it did work!

 

[GlennUK]

Hooray! Glad you got there.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.