Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Same VBA code, different run time
Hello all,
Following code works well at daytime. It usually took less then 1 mins. However, when I scheduled it running at night, it usually took more then 10 mins. The PC is desktop, opeation system is Win2000, Excel2000. The main difference between day and night is nobody touch the desk PC at night. I have setup the power alway onn, but no help. Does anybody have idea how to fix it? Thanks a lot! Sub Get_Data() Dim d1 As Range Dim d2 As Range dim i as long Dim j As Integer Dim k As Integer i=3000 Sheets("Query5").Select Set d1 = Range(Cells(2, 3), Cells(i - 1, 3)) Sheets("Query2").Select Columns("C:C").Select Selection.ClearContents Columns("J:J").Select Selection.ClearContents For j = 2 To 20000 If Cells(j, 4) = "" Then Exit For End If Cells(j, 3) = Mid(Cells(j, 4), 1, 8) With d1 Set d2 = .Find(Cells(j, 4), LookIn:=xlValue, lookat:=xlWhole) End With If Not d2 Is Nothing Then Cells(j, 10) = 0 Else Cells(j, 10) = 1 End If Next Columns("C:J").Select Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Key2:=Range("I1") _ , Order2:=xlDescending, Key3:=Range("D1"), Order3:=xlDescending, _ Header:=xlYes, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Range("C1").Select End Sub Best Regards Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Same VBA code, different run time
This page lists some suggestions and strategies for optimizing your
Visual Basic For Applications (VBA) code, so that it will run faster. http://www.cpearson.com/excel/optimize.htm It seems to me that one place to start is to find out where (if) the loop exits prematurely. Maybe there is more data in Column 4 at night (20 versus 20,000 iterations). Especially since you have not turned off ScreenUpdating and Calculation explicitly. You may have formulas that are being recalculated. HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- Hello all, Following code works well at daytime. It usually took less then 1 mins. However, when I scheduled it running at night, it usually took more then 10 mins. The PC is desktop, opeation system is Win2000, Excel2000. The main difference between day and night is nobody touch the desk PC at night. I have setup the power alway onn, but no help. Does anybody have idea how to fix it? Thanks a lot! Sub Get_Data() Dim d1 As Range Dim d2 As Range dim i as long Dim j As Integer Dim k As Integer With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With i=3000 Sheets("Query5").Select Set d1 = Range(Cells(2, 3), Cells(i - 1, 3)) Sheets("Query2").Select Columns("C:C").ClearContents Columns("J:J").ClearContents For j = 2 To 20000 If Cells(j, 4) = "" Then Debug.Print j Exit For End If Cells(j, 3) = Mid(Cells(j, 4), 1, 8) With d1 Set d2 = .Find(Cells(j, 4), LookIn:=xlValue, lookat:=xlWhole) End With If Not d2 Is Nothing Then Cells(j, 10) = 0 Else Cells(j, 10) = 1 End If Next Columns("C:J").Sort Key1:=Range("J1"), Order1:=xlAscending, Key2:=Range("I1") _ , Order2:=xlDescending, Key3:=Range("D1"), Order3:=xlDescending, _ Header:=xlYes, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Range("C1").Select With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub Best Regards Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time with VBA code | Excel Discussion (Misc queries) | |||
Time code | Excel Discussion (Misc queries) | |||
smpte time code | Excel Discussion (Misc queries) | |||
TIME CLOCK CODE | Excel Discussion (Misc queries) | |||
Code for entering time - trying again | Excel Discussion (Misc queries) |