Hi,

Can I enforce Goal Seek to perform one iteration only?

e.g. I want to have the equation in A1 result in zero (0) by changing B1.
However, because there are several possible B1 values that will yield 0 in
A1, Goal Seek will carry out several iterations and not able to yield a
single answer.

I'd like to be able to manually input an initial guessed value in B1 first,
then run Goal Seek to find the exact value, that is closest to the guessed
value, that will yield zero in A1. (I had successfully done this once using
the macro below, but somehow the second one I try to creat on the same
worksheet failed to give a single answer as aforementioned. Why is this??)

Sub Macro1()
Range("A1").Select
Range("A1").GoalSeek Goal:=0,ChangingCell:=Range("B1")
End Sub

Go to Tools menu > Options > Calculation, and change Maximum Iterations to
1. Record a macro while doing it, so you know how to change it, and change
it back, in code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Sam Kuo" <(E-Mail Removed).(donotspam)> wrote in message
news:9CB6B040-2A3E-40FE-B416-(E-Mail Removed)...
> Hi,
>
> Can I enforce Goal Seek to perform one iteration only?
>
> e.g. I want to have the equation in A1 result in zero (0) by changing B1.
> However, because there are several possible B1 values that will yield 0 in
> A1, Goal Seek will carry out several iterations and not able to yield a
> single answer.
>
> I'd like to be able to manually input an initial guessed value in B1
> first,
> then run Goal Seek to find the exact value, that is closest to the guessed
> value, that will yield zero in A1. (I had successfully done this once
> using
> the macro below, but somehow the second one I try to creat on the same
> worksheet failed to give a single answer as aforementioned. Why is this??)
>
> Sub Macro1()
> Range("A1").Select
> Range("A1").GoalSeek Goal:=0,ChangingCell:=Range("B1")
> End Sub

I meant while changing the maximum iterations, so your code can set it to
one value, then reset it when finished.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Sam Kuo" <(E-Mail Removed).(donotspam)> wrote in message
news:F3E1F8F1-07D8-439C-AEC0-(E-Mail Removed)...
> Thanks Jon. I've change Maximum Iteration to 1, but it still doesn't do
> just
> one iteration??
> Can you please explain in more detail what you mean when you say to
> "record
> a macro while doing it and change it back in code"? - sorry I'm a newby
> to
> VBA coding. Thank you
>
> "Jon Peltier" wrote:
>
>> Go to Tools menu > Options > Calculation, and change Maximum Iterations
>> to
>> 1. Record a macro while doing it, so you know how to change it, and
>> change
>> it back, in code.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "Sam Kuo" <(E-Mail Removed).(donotspam)> wrote in message
>> news:9CB6B040-2A3E-40FE-B416-(E-Mail Removed)...
>> > Hi,
>> >
>> > Can I enforce Goal Seek to perform one iteration only?
>> >
>> > e.g. I want to have the equation in A1 result in zero (0) by changing
>> > B1.
>> > However, because there are several possible B1 values that will yield 0
>> > in
>> > A1, Goal Seek will carry out several iterations and not able to yield a
>> > single answer.
>> >
>> > I'd like to be able to manually input an initial guessed value in B1
>> > first,
>> > then run Goal Seek to find the exact value, that is closest to the
>> > guessed
>> > value, that will yield zero in A1. (I had successfully done this once
>> > using
>> > the macro below, but somehow the second one I try to creat on the same
>> > worksheet failed to give a single answer as aforementioned. Why is
>> > this??)
>> >
>> > Sub Macro1()
>> > Range("A1").Select
>> > Range("A1").GoalSeek Goal:=0,ChangingCell:=Range("B1")
>> > End Sub
>>
>>
>>