I have some code that looks through 10032 entries with 10020 entries so a double loop It was taking about 1 minute and 45 seconds Now for some reason it takes just under 4 minutes Is there a better/Faster way
Code:
Sub FindDifference()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim AircraftFound As Boolean
Dim Sht As Worksheet
Dim RowCount As Integer, RowCount2 As Integer
' find out how many rows are needed
' First check worksheet (Aircraft Data)
Set Sht = Worksheets("OrigCheck")
RowCount = Sht.Cells(Sht.Rows.Count, "B").End(xlUp).Row
' Now Check worksheet (NewData)
Set Sht = Worksheets("NewData")
RowCount2 = Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row
If RowCount2 > RowCount Then RowCount = RowCount2
' Add one so the number we are checking is the same or higher
' since one sheet starts on cell A1 and the other starts on Cell A2
RowCount = RowCount + 1
strRangeToCheck = "G" & RowCount
' set arrays instead of looping through a range to speed up the process
varSheetA = Worksheets("NewData").Range("A1:" & strRangeToCheck)
varSheetB = Worksheets("OrigCheck").Range("A2:" & strRangeToCheck)
Debug.Print Now
For j = LBound(varSheetA, 1) To UBound(varSheetA, 1)
If varSheetA(j, 1) <> "" Then
For jj = LBound(varSheetB, 1) To UBound(varSheetB, 1)
If UCase(varSheetA(j, 1)) = UCase(varSheetB(jj, 1)) And _
UCase(varSheetA(j, 2)) = UCase(varSheetB(jj, 2)) And _
UCase(varSheetA(j, 3)) = UCase(varSheetB(jj, 3)) And _
UCase(varSheetA(j, 4)) = UCase(varSheetB(jj, 4)) And _
UCase(varSheetA(j, 5)) = UCase(varSheetB(jj, 5)) And _
UCase(varSheetA(j, 6)) = UCase(varSheetB(jj, 6)) And _
UCase(varSheetA(j, 7)) = UCase(varSheetB(jj, 7)) Then
AircraftFound = True
GoTo Skip1
End If
Next
Skip1:
'Now if a differance is found add it to the "Diff File" worksheet
If AircraftFound = True Then
AircraftFound = False
Else
For Each cell In Range("DiffManufacturer")
If cell.Value = "" Then
cell.Value = varSheetA(j, 1)
cell.Offset(0, 1) = varSheetA(j, 2)
cell.Offset(0, 2) = varSheetA(j, 3)
cell.Offset(0, 3) = varSheetA(j, 4)
cell.Offset(0, 4) = varSheetA(j, 5)
cell.Offset(0, 5) = varSheetA(j, 6)
cell.Offset(0, 6) = varSheetA(j, 7)
' cell.Offset(0, 7) = varSheetA(j, 8)
Exit For
End If
Next
End If
End If
Next
Debug.Print Now
End Sub