Help with better loop code
 

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

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