I've written a bit of code designed to go through a log file and determine successes and failures for certain criteria for each server in an environment. The code works just fine, except that on environments of larger size, it's just too slow. Currently it's running on a 2000 server environment with 18 criteria and 5000 lines of logs being checked and takes about 14 minutes on my machine. The problem is that it's intended to be used on an environment of about 18000 servers with about 40 criteria and 175,000 lines of logs.
I'm hoping someone can help me come up with something I'm not seeing right now that would help with the speed, because waiting an hour and hoping Excel doesn't crash doesn't really speed up this process.
Here's what I'm trying to do:
This is the code I'm running (there are comments in there to help understanding):
Dim lastCol As Double, lastRow As Double, lastSensorRow As Double
Sub B_L3ToolRun()
SpeedUp
Dim numSuc As Long, numMaj As Long, numWar As Long, numCri As Long, numErr As Long
lastRow = Sheets("Results").Range("A" & Rows.Count).End(xlUp).Row //End of IPs
lastSensorRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row //End of Log File
lastCol = 3 //Minimum columns used
For I = 4 To 150 //Discovers number of criteria used
If Cells(1, I).Value <> "" Then
lastCol = lastCol + 1
End If
Next I
Sheets("Results").Activate
For J = 3 To lastCol //This is a check for each criteria being checked
For I = 2 To lastRow //This is a check for each IP in the list (2000)
numSuc = WorksheetFunction.CountIfs(Sheets("Data").Range("B:B"), "*" & Sheets("Results").Cells(1, J).Value & "*", _ //This checks for number of given criteria
Sheets("Data").Range("E:E"), "=normal", _ //This refines it to only successful ones
Sheets("Data").Range("C:C"), "*" & Sheets("Results").Cells(I, 1).Value & "*") //This limits that to number of successful on that IP
numMaj = WorksheetFunction.CountIfs(Sheets("Data").Range("B:B"), "*" & Sheets("Results").Cells(1, J).Value & "*", _
Sheets("Data").Range("E:E"), "=major", _ //Same as above but "major" error
Sheets("Data").Range("C:C"), "*" & Sheets("Results").Cells(I, 1).Value & "*")
numWar = WorksheetFunction.CountIfs(Sheets("Data").Range("B:B"), "*" & Sheets("Results").Cells(1, J).Value & "*", _
Sheets("Data").Range("E:E"), "=warning", _ //Same as above but "warning" error
Sheets("Data").Range("C:C"), "*" & Sheets("Results").Cells(I, 1).Value & "*")
numCri = WorksheetFunction.CountIfs(Sheets("Data").Range("B:B"), "*" & Sheets("Results").Cells(1, J).Value & "*", _
Sheets("Data").Range("E:E"), "=critical", _ ////Same as above but "critical" error
Sheets("Data").Range("C:C"), "*" & Sheets("Results").Cells(I, 1).Value & "*")
numErr = numMaj + numWar + numCri
If numErr > 0 Or numSuc > 0 Then
Sheets("Results").Cells(I, J).Value = numSuc & "/" & numErr //I want them displayed in this format "count success/count fail"
End If
Next I
Next J
SlowDown
End Sub
Private Sub SpeedUp()
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Application.DisplayAlerts = False
End Sub
Private Sub SlowDown()
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = displayPageBreaksState
Application.DisplayAlerts = True
End Sub