for/do until loop in excel

Kotepie

Board Regular
Joined
Sep 1, 2010
Messages
55
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

dafan

Well-known Member
Joined
May 6, 2008
Messages
692
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:
Code:
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.
 

Forum statistics

Threads
1,141,728
Messages
5,708,137
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top