goal seek function

[alexandria.parrish]

Is there any way to macro the goal seek function?

I am trying to calculate an investment rate of return in a given
year. The goal seek function requires that the goal be typed into the
middle box in order to calculate. Is there any way around this?

 

[joeu2004]

Is there any way to macro the goal seek function?

I am trying to calculate an investment rate of return in a given
year. The goal seek function requires that the goal be typed into the
middle box in order to calculate. Is there any way around this?

Try using Tools => Macro => Record New Macro to see how its done.

But are you sure that you need Goal Seek to solve your problem?

I do a lot of IRR calculations, and I have never used Goal Seek. In
fact, I cannot even get the example on the GS help page to work. (And
that is an example of a problem that does not require GS.)

Perhaps if you describe the data that you have, we can offer
alternative approaches that are more direct.

 

[Wondering]

I put a 5 in cell A5, and in A6, the formula =a5+1 then I ran:

Sub GS()
Range("A6").GoalSeek Goal:=10, ChangingCell:=Range("A5")
End Sub

After running this, A5 was 9 and A10 showed 10

 

[Wondering]

Correction:

I put a 5 in cell A5, and in A6, the formula =a5+1 then I ran:

Sub GS()
Range("A6").GoalSeek Goal:=10, ChangingCell:=Range("A5")
End Sub

After running this, A5 was 9 and A6 showed 10