PDA

View Full Version : Continuous Looping, help!

doctortt
11-20-2012, 04:26 PM
Hi, Can a guru help? The loop keeps on going even though the three cells have become 0. What did I do wrong?

Do

Range(hello1).GoalSeek Goal:=0, ChangingCell:=Range("A123")
Range(hello2).GoalSeek Goal:=0, ChangingCell:=Range("A124")
Range(hello3).GoalSeek Goal:=0, ChangingCell:=Range("A125")

Loop Until Range(hello1).Value = 0 And Range(hello2).Value = 0 And Range(hello3).Value = 0

p45cal
11-20-2012, 05:18 PM
Well that really depends on the formulae in the cells!
Or conside using Solver which allows you to control the number of iterations and or the time allowed.

Paul_Hossler
11-20-2012, 06:13 PM
Also are you sure they're = 0.000000000000000000.... ?

Maybe

Abs(Range(hello1).Value) <= 0.000001

Paul

Teeroy
11-20-2012, 06:50 PM
Also consider whether the 3 hello ranges are independent of each other and the other 2 change ranges (other than selected for each goal seek). If not then you can (and will) chase your tail (as you are trying to solve a set of simultaneous equations).

doctortt
11-20-2012, 10:35 PM
Gentlemen,

Do you think another loop method may work?

Teeroy
11-20-2012, 11:30 PM
To answer both @P45cal's questions and mine I think we need to see a sample workbook before suggesting an alternate approach.

doctortt
11-21-2012, 12:13 PM
Oh, I used Excel's built-in solver, and it's working the way I want it to be.

Teeroy
11-21-2012, 02:09 PM
Good to hear. The solver gives you a lot more control of the process and you can use the constraints to reduce the chance of divergence but if your initial guess is too far out it's still not perfect.