Help with better loop code
Perhaps this ?:
Stepno = 1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For c = 14 To 20 '<===== Change 20 to Suit
Stepno = Stepno + 1
UserForm1.Label1.Caption = "Step " & Stepno & " in progress"
UserForm1.Repaint
Application.StatusBar = "Step " & Stepno & " in progress"
' For R=50 to 1187 step 4 '<=== Replace "For" statement below and remove
R=50 + counter ????
For counter = 0 To 1137 Step 4 ' Inner loop.
R = 50 + counter 'start row
If Cells(R - 3, 1).Value = "Y" And Cells(R - 3, 2).Value = 1 Then
Cells(R - 1, c).Clear
If Cells(R, c).Value = 0 Then
If Cells(R - 3, 10).Value = 1 Then
Cells(R - 1, C).Value = (Cells(R - 2, C).Value - Cells(R -
3, C - 2).Value) _
+ ((Cells(R - 2, C + 1).Value) * (Cells(R - 3, 11).Value) /
4)
ElseIf Cells(R - 3, 10).Value = 2 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 3 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 4 Then
"what ever"
Else: Cells(R - 1, c).Value = Cells(R - 3, 9).Value
End If
ElseIf (Cells(R, c).Value < (Cells(R - 2, c + 1).Value) * (Cells(R -
3, 11).Value) / 4) Then
If Cells(R - 3, 10).Value = 1 Then
ElseIf Cells(R - 3, 10).Value = 2 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 3 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 4 Then
"what ever"
Else: Cells(R - 1, c).Value = Cells(R - 3, 9).Value
End If
End If
End If
Next counter
' Next R <=== if Counter loop is removed
Next c
"Sliman" wrote:
Have following code that works but am sure there is a better way:-
UserForm1.Label1.Caption = "Step 2 in progress"
UserForm1.Repaint
Application.StatusBar = "Step 2 in progress"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Check = True: Counter = 0: R = 50 ' Initialize variables.
Do ' Outer loop.
Do While Counter < 1137 ' Inner loop.
R = 50 + Counter 'start row
C = 14 'start Column
If Cells(R - 3, 1).Value = "Y" And Cells(R - 3, 2).Value = 1 Then
Cells(R - 1, C).Clear
If Cells(R, C).Value = 0 Then
If Cells(R - 3, 10).Value = 1 Then
Cells(R - 1, C).Value = (Cells(R - 2, C).Value - Cells(R -
3, C - 2).Value) _
+ ((Cells(R - 2, C + 1).Value) * (Cells(R - 3, 11).Value) /
4)
ElseIf Cells(R - 3, 10).Value = 2 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 3 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 4 Then
"what ever"
Else: Cells(R - 1, C).Value = Cells(R - 3, 9).Value
End If
ElseIf (Cells(R, C).Value < (Cells(R - 2, C + 1).Value) *
(Cells(R - 3, 11).Value) / 4) Then
If Cells(R - 3, 10).Value = 1 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 2 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 3 Then
"what ever"
ElseIf Cells(R - 3, 10).Value = 4 Then
"what ever"
Else: Cells(R - 1, C).Value = Cells(R - 3, 9).Value
End If
End If
End If
Counter = Counter + 4 ' Increment Counter.
If Counter 1137 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
UserForm1.Label1.Caption = "Step 3 in progress"
UserForm1.Repaint
Application.StatusBar = "Step 3 in progress"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Check = True: Counter = 0: R = 50 ' Initialize variables.
Do ' Outer loop.
Do While Counter < 1137 ' Inner loop.
R = 50 + Counter 'start row
C = 15 'start Column
If Cells(R - 3, 1).Value = "Y" And Cells(R - 3, 2).Value = 1 Then
Cells(R - 1, C).Clear
If Cells(R, C).Value = 0 Then
If Cells(R - 3, 10).Value = 1 Then
"what ever"
carrys on as same loop above but on next column
Thanks for any guidance
Simon
|