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

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