Selected Answer
Before I answer your question let me suggest a few changes to your current setup. For one, you should declare all variables. Adding "Option Explicit" at the top of the sheet helps do this, avoids typos, and generally prolongs your useful life. Then, your code should have a function, like this.
Option Explicit
Sub Run_Test()
Dim i As Double
i = Range("_B3_Percent").Value
Do While i < 0.75
i = Back_3_Success(i)
Loop
End Sub
Private Function Back_3_Success(ByVal i As Double) As Double
' at start i = Range("_B3_Percent").Value
' the following simulates your calculations to reduce i
i = i - 0.1
Range("_B3_Percent").Value = i
Back_3_Success = i
End Function
The function is important in the loop because it shows how the value i gets changed, not discernible in your original code. The other thing it does is to avoid accessing the worksheet multiple times. Each read/write takes a lot of time. Doing the same thing with a variable is at least 50 times faster.
Now, in order to look at multiple rows, one after the other, you need to get rid of the fixed range name and substitute it with a variable range. Actually, you don't need a range at all. All that's required is a row number.
With this technique added to the original code the result will look like this.
Option Explicit
Sub Run_Test()
Dim i As Double
Dim Rl As Long ' last row
Dim R As Long
With ActiveSheet
' find the last filled cell in column B
' and assign its row number to the variable Rl
Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
' now loop through all rows from 3rd row to Rl
For R = 3 To Rl
i = .Cells(R, "B").Value
Do While i < 0.75
i = Back_3_Success(i, R)
Loop
Next R
End With
End Sub
Private Function Back_3_Success(ByVal i As Double, _
R As Long) As Double
' at start i = Cells(R, "B").Value
' the following simulates your calculations to reduce i
i = i - 0.1
ActiveSheet.Cells(R, "B").Value = i
Back_3_Success = i
End Function
You can use idential code but looking for values >0.75. Or you could make a small modification in the above to the same effect.
Option Explicit
Sub Run_Test()
Dim i As Double
Dim Rl As Long ' last row
Dim R As Long
With ActiveSheet
' find the last filled cell in column B
' and assign its row number to the variable Rl
Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
' now loop through all rows from 3rd row to Rl
For R = 3 To Rl
i = .Cells(R, "B").Value
If i > 0.75 Then
Do
i = Forward_3_Success(i, R)
Loop While i > 0.75
Else
Do While i < 0.75
i = Back_3_Success(i, R)
Loop
End If
Next R
End With
End Sub
Private Function Back_3_Success(ByVal i As Double, _
R As Long) As Double
' at start i = Cells(R, "B").Value
' the following simulates your calculations to reduce i
i = i - 0.1
ActiveSheet.Cells(R, "B").Value = i
Back_3_Success = i
End Function
Private Function Forward_3_Success(ByVal i As Double, _
R As Long) As Double
' at start i = Cells(R, "B").Value
' the following simulates your calculations to increase i
i = i + 0.1
ActiveSheet.Cells(R, "B").Value = i
Forward_3_Success = i
End Function
Now the code calls either the 'Forward' or 'Back' version of the function where the value in column B is modified until it equals 0.75.
