for/do until loop in excel

[Kotepie]

Hi,
I have a solver set up in excel to keep the value of Y23 equal to 0.0005 by changing the value of N23. I also want to eb able to loop it so it does that for every line (ie. Y24 to equal 0.0005 by changing N24 and so on...) until it encounters a blank cell. I got Solver to work for it, but not the looping code. I keep getting an error at the "Do Until" line. here is my code:


Sub Stat()
'
Do Until Cells(rctr, 23).Value = ""
rctr = rctr + 1
rctr = 23

'
SolverReset
SolverOk SetCell:="$Y$23", MaxMinVal:=3, ValueOf:=0.0005, ByChange:="$N$23", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$N$23", Relation:=3, FormulaText:="0.0001"
SolverOk SetCell:="$Y$23", MaxMinVal:=3, ValueOf:=0.0005, ByChange:="$N$23", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$Y$23", MaxMinVal:=3, ValueOf:=0.0005, ByChange:="$N$23", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve True
Loop

End Sub



any help would be appreciated, Thanks.

 

[dafan]

First off, you have to define rctr before you use it in Cells()
VBA doesn't know what you mean by rctr!

Then you are 're'setting rctr to 23 on you 2nd line in your loop. This will keep the 'Cells' part on 23,23 which will never be "", therefore hanging.

So first part should be:

rctr = 23
Do Until Cells(rctr, 23).Value = ""
rctr = rctr + 1

In the rest of your code you are Solving for Y23 multiple times. I suggest recording it once then putting the new code in.
Then you have to replace SetCell:="$Y$23" with a variable made of rctr and 23.

I have no clue atm how to....maybe someone else.

Hope this helps.