# for/do until loop in excel

#### Kotepie

##### Board Regular
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"
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

Replies
1
Views
548
Replies
1
Views
289
Replies
7
Views
868
Replies
0
Views
1K
Replies
0
Views
946

1,211,696
Messages
6,103,354
Members
447,861
Latest member
LllopezXC

### 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.

### Which adblocker are you using?

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

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