Make the code crisper to enhance performance

[tariq_kamal]

Dear all,

below is the code that i am using to analyze some data. The code is taking around 2.2 minutes to run. Being a report for senior management this is too long. Please suggest ways so that it runs faster.

Option Explicit
Dim rw As Long
Dim i As Integer
Dim count As Integer
 
Sub Summary()
Dim j As Integer
Dim ud As Long
Dim dd As Long
Dim dd1 As Long
Dim dd2 As Long
Dim dd3 As Long
Application.ScreenUpdating = False

Sheets("Tracing").Range(Cells(42, 2), Cells(53, 4)).Value = ""
Sheets("Tracing").Range(Cells(57, 2), Cells(68, 4)).Value = ""
Sheets("Tracing").Range(Cells(72, 2), Cells(83, 4)).Value = ""
count = Sheets("lockdown").Cells(1, 13).Value
Worksheets("Lockdown").Activate
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Assigned games for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

For j = 42 To 53
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count
dd = Sheets("Lockdown").Cells(i, 19).Value
If DateDiff("m", ud, dd) = 0 Then
Sheets("Tracing").Cells(j, 2).Value = Sheets("Tracing").Cells(j, 2).Value + 1
End If
Next i
Next j
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Returned games for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'*********************************************************
'Definition of variables and setting values for variables
'*********************************************************
For j = 42 To 53
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count

dd = Sheets("Lockdown").Cells(i, 23).Value
dd1 = Sheets("lockdown").Cells(i, 26).Value
dd2 = Sheets("Lockdown").Cells(i, 29).Value
dd3 = Sheets("Lockdown").Cells(i, 32).Value
'***************************************************************************************
'Check if game is not completed if only one cycle then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (DateDiff("m", ud, dd) = 0 And Cells(i, 26).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'***************************************************************************************
'Check if game is not completed if only two cycles then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (DateDiff("m", ud, dd1) = 0 And Cells(i, 29).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'***************************************************************************************
'Check if game is not completed if only three cycles then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (DateDiff("m", ud, dd2) = 0 And Cells(i, 32).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'*********************************************************************************
'Check if game is not completed if four cycles then return date for specific month
'*********************************************************************************
If Cells(i, 9).Value <> "Completed" And DateDiff("m", ud, dd3) = 0 Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'**************************************************************************
'Missed logic - If game is completed then pick data for its returned cycles
'**************************************************************************
If (Cells(i, 9).Value = "Completed") And (DateDiff("m", ud, dd) = 0 And Cells(i, 26).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
If (Cells(i, 9).Value = "Completed") And (DateDiff("m", ud, dd1) = 0 And Cells(i, 29).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
If (Cells(i, 9).Value = "Completed") And (DateDiff("m", ud, dd2) = 0 And Cells(i, 32).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else

End If
End If
End If
End If
End If
End If
End If
Next i
Next j
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Completed games for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'*********************************************************
'Definition of variables and setting values for variables
'*********************************************************
For j = 42 To 53
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count

dd = Sheets("Lockdown").Cells(i, 14).Value
'********************************************************************************
'Check if game is completed if only one cycle then return date for specific month
'********************************************************************************
If Cells(i, 9).Value = "Completed" And DateDiff("m", ud, dd) = 0 Then
Sheets("Tracing").Cells(j, 4).Value = Sheets("Tracing").Cells(j, 4).Value + 1
End If
Next i
Next j

'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Assigned MGL/Port games for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

For j = 57 To 68
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count

dd = Sheets("Lockdown").Cells(i, 19).Value
If DateDiff("m", ud, dd) = 0 And (Cells(i, 17).Value <> "TA") Then
Sheets("Tracing").Cells(j, 2).Value = Sheets("Tracing").Cells(j, 2).Value + 1
End If
Next i
Next j
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Returned games MGL/Port for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'*********************************************************
'Definition of variables and setting values for variables
'*********************************************************
For j = 57 To 68
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count

dd = Sheets("Lockdown").Cells(i, 23).Value
dd1 = Sheets("lockdown").Cells(i, 26).Value
dd2 = Sheets("Lockdown").Cells(i, 29).Value
dd3 = Sheets("Lockdown").Cells(i, 32).Value
'***************************************************************************************
'Check if game is not completed if only one cycle then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value <> "TA") And (DateDiff("m", ud, dd) = 0 And Cells(i, 26).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'***************************************************************************************
'Check if game is not completed if only two cycles then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value <> "TA") And (DateDiff("m", ud, dd1) = 0 And Cells(i, 29).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'***************************************************************************************
'Check if game is not completed if only three cycles then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value <> "TA") And (DateDiff("m", ud, dd2) = 0 And Cells(i, 32).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'*********************************************************************************
'Check if game is not completed if four cycles then return date for specific month
'*********************************************************************************
If Cells(i, 9).Value <> "Completed" And (Cells(i, 17).Value <> "TA") And DateDiff("m", ud, dd2) = 0 Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'**************************************************************************
'Missed logic - If game is completed then pick data for its returned cycles
'**************************************************************************
If (Cells(i, 9).Value = "Completed") And (Cells(i, 17).Value <> "TA") And (DateDiff("m", ud, dd) = 0 And Cells(i, 26).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
If (Cells(i, 9).Value = "Completed") And (Cells(i, 17).Value <> "TA") And (DateDiff("m", ud, dd1) = 0 And Cells(i, 29).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
If (Cells(i, 9).Value = "Completed") And (Cells(i, 17).Value <> "TA") And (DateDiff("m", ud, dd2) = 0 And Cells(i, 32).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else

End If
End If
End If
End If
End If
End If
End If
Next i
Next j
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Completed MGL/Port games for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'*********************************************************
'Definition of variables and setting values for variables
'*********************************************************
For j = 57 To 68
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count

dd = Sheets("Lockdown").Cells(i, 14).Value
'********************************************************************************
'Check if game is completed if only one cycle then return date for specific month
'********************************************************************************
If Cells(i, 9).Value = "Completed" And DateDiff("m", ud, dd) = 0 And (Cells(i, 17).Value <> "TA") Then
Sheets("Tracing").Cells(j, 4).Value = Sheets("Tracing").Cells(j, 4).Value + 1
End If
Next i
Next j
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Assigned TA games for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

For j = 72 To 83
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count

dd = Sheets("Lockdown").Cells(i, 19).Value
If DateDiff("m", ud, dd) = 0 And (Cells(i, 17).Value = "TA") Then
Sheets("Tracing").Cells(j, 2).Value = Sheets("Tracing").Cells(j, 2).Value + 1
End If
Next i
Next j

'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Returned games TA for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'*********************************************************
'Definition of variables and setting values for variables
'*********************************************************
For j = 72 To 83
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count

dd = Sheets("Lockdown").Cells(i, 23).Value
dd1 = Sheets("lockdown").Cells(i, 26).Value
dd2 = Sheets("Lockdown").Cells(i, 29).Value
dd3 = Sheets("Lockdown").Cells(i, 32).Value
'***************************************************************************************
'Check if game is not completed if only one cycle then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd) = 0 And Cells(i, 26).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'***************************************************************************************
'Check if game is not completed if only two cycles then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd1) = 0 And Cells(i, 29).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'***************************************************************************************
'Check if game is not completed if only three cycles then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd2) = 0 And Cells(i, 32).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'*********************************************************************************
'Check if game is not completed if four cycles then return date for specific month
'*********************************************************************************
If Cells(i, 9).Value <> "Completed" And (Cells(i, 17).Value = "TA") And DateDiff("m", ud, dd2) = 0 Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'**************************************************************************
'Missed logic - If game is completed then pick data for its returned cycles
'**************************************************************************
If (Cells(i, 9).Value = "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd) = 0 And Cells(i, 26).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
If (Cells(i, 9).Value = "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd1) = 0 And Cells(i, 29).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
If (Cells(i, 9).Value = "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd2) = 0 And Cells(i, 32).Value <> "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
End If
End If
End If
End If
End If
End If
End If
Next i
Next j
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Create summary for Completed TA games for all months
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'*********************************************************
'Definition of variables and setting values for variables
'*********************************************************
For j = 72 To 83
ud = Sheets("tracing").Cells(j, 1).Value
For i = 4 To count

dd = Sheets("Lockdown").Cells(i, 14).Value
'********************************************************************************
'Check if game is completed if only one cycle then return date for specific month
'********************************************************************************
If Cells(i, 9).Value = "Completed" And DateDiff("m", ud, dd) = 0 And (Cells(i, 17).Value = "TA") Then
Sheets("Tracing").Cells(j, 4).Value = Sheets("Tracing").Cells(j, 4).Value + 1
End If
Next i
Next j

Application.ScreenUpdating = True
End Sub

 

[sous2817]

Without seeing the workbook, it'd be hard to see why it's giving you that message. I sent you a private message (PM).

 

[tariq_kamal]

I will send you the workbook...

 

[jstiene]

As already stated if you are running loops on the same rows, you might as well do as much as possible on one loop and not repeat it.

Also you could take whats common like

If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value = "TA") then
'individual conditions
if (DateDiff("m", ud, dd1) = 0 And Cells(i, 29).Value = "") Then

end if

if (DateDiff("m", ud, dd2) = 0 And Cells(i, 29).Value = "") Then

end if

end if

of course if you delade ud and dd as dates, I believe you can just say if ud-dd4 then
because Excel will understand it as the number of days since Jan 1 1900 anyway. Or maybe your long either way, difference = 0 may take more resources.
end if

Or find the more rare situation, Completed or TA and put that in an outer loop to force it to skip over more often and run faster.

You could also filter columns to Completed and TA copy and paste the smaller set and run from there.








If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd) = 0 And Cells(i, 26).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'***************************************************************************************
'Check if game is not completed if only two cycles then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd1) = 0 And Cells(i, 29).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else
'***************************************************************************************
'Check if game is not completed if only three cycles then return date for specific month
'***************************************************************************************
If (Cells(i, 9).Value <> "Completed") And (Cells(i, 17).Value = "TA") And (DateDiff("m", ud, dd2) = 0 And Cells(i, 32).Value = "") Then
Sheets("Tracing").Cells(j, 3).Value = Sheets("Tracing").Cells(j, 3).Value + 1
Else

 

[jstiene]

I meant declare them as dates and see if they are equal. But as longs you should be able to get the same thing. Why use date diff when you only want equality not 30 days, 90 days, etc.

 

[tariq_kamal]

Hi Jstiene,

Thnx for highlighting these. Well i tried what SOUS suggested and the code ran like a bullet..But i surely will keep into mind what you have suggested teh next time i am writing my codes. Thnx to you. And loads of thnx to SOUS for his help..

 

[jstiene]

No problem. Its hard to optimize blind.