| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop for VBA code? | Excel Worksheet Functions | |||
| Loop Code | Excel Programming | |||
| How to Loop some code | Excel Discussion (Misc queries) | |||
| How to get my code to loop | Excel Programming | |||
| Help with loop code... | Excel Programming | |||
Linear Mode
