Same VBA code, different run time

  • Thread starter Thread starter Bill Li
  • Start date Start date
B

Bill Li

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
 
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
Click to expand...

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
Click to expand...

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
Click to expand...

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
 
Back
Top