Private Sub cmdCompare_Click()
Dim strTable(1000, 24) As String
Dim strTableResult(1000, 24) As String
Dim strTimeBox As String
Dim intRow As Long
Dim intIndex As Long
Dim intIndexResult As Long
Dim blnFounded As Boolean
'Alert the user that this will take some time
strTimeBox = MsgBox("This process can take up to two minutes. Do not touch your mouse or keyboard during this time.", vbInformation, "Please be Patient...")
'Load table with search values
Sheets(1).Select
intRow = 2 'start reading on row 2, as row 1 is the heading
intIndex = 0
Do While ActiveSheet.Cells(intRow, 1).Value <> ""
strTable(intIndex, 0) = ActiveSheet.Cells(intRow, 1)
intIndex = intIndex + 1
intRow = intRow + 1
Loop
'Search list
Workbooks.Open "\\Mddp-oa-020\MddUsrCrtI\User Certification Listing.xls"
intIndex = 0
intIndexResult = 0
Do While strTable(intIndex, 0) <> ""
intRow = 2
blnFounded = False
Do While ActiveSheet.Cells(intRow, 1).Value <> ""
If ActiveSheet.Cells(intRow, 1).Value = strTable(intIndex, 0) Then
blnFounded = True
Exit Do
End If
intRow = intRow + 1
Loop
If blnFounded = True Then
'Load result in result-table
strTableResult(intIndexResult, 0) = ActiveSheet.Cells(intIndex, 1)
strTableResult(intIndexResult, 1) = ActiveSheet.Cells(intIndex, 2)
strTableResult(intIndexResult, 2) = ActiveSheet.Cells(intIndex, 3)
strTableResult(intIndexResult, 3) = ActiveSheet.Cells(intIndex, 4)
strTableResult(intIndexResult, 4) = ActiveSheet.Cells(intIndex, 5)
strTableResult(intIndexResult, 5) = ActiveSheet.Cells(intIndex, 6)
strTableResult(intIndexResult, 6) = ActiveSheet.Cells(intIndex, 6)
strTableResult(intIndexResult, 7) = ActiveSheet.Cells(intIndex, 7)
strTableResult(intIndexResult, 8) = ActiveSheet.Cells(intIndex, 8)
strTableResult(intIndexResult, 9) = ActiveSheet.Cells(intIndex, 9)
strTableResult(intIndexResult, 10) = ActiveSheet.Cells(intIndex, 10)
strTableResult(intIndexResult, 11) = ActiveSheet.Cells(intIndex, 11)
strTableResult(intIndexResult, 12) = ActiveSheet.Cells(intIndex, 12)
strTableResult(intIndexResult, 13) = ActiveSheet.Cells(intIndex, 13)
strTableResult(intIndexResult, 14) = ActiveSheet.Cells(intIndex, 14)
strTableResult(intIndexResult, 15) = ActiveSheet.Cells(intIndex, 15)
strTableResult(intIndexResult, 16) = ActiveSheet.Cells(intIndex, 16)
strTableResult(intIndexResult, 17) = ActiveSheet.Cells(intIndex, 17)
strTableResult(intIndexResult, 18) = ActiveSheet.Cells(intIndex, 18)
strTableResult(intIndexResult, 19) = ActiveSheet.Cells(intIndex, 19)
strTableResult(intIndexResult, 20) = ActiveSheet.Cells(intIndex, 20)
strTableResult(intIndexResult, 21) = ActiveSheet.Cells(intIndex, 21)
strTableResult(intIndexResult, 22) = ActiveSheet.Cells(intIndex, 22)
strTableResult(intIndexResult, 23) = ActiveSheet.Cells(intIndex, 23)
strTableResult(intIndexResult, 24) = ActiveSheet.Cells(intIndex, 24)
intIndexResult = intIndexResult + 1
End If
intIndex = intIndex + 1
Loop
ActiveWorkbook.Close
'Writing result table in sheet results
Sheets(1).Select
intIndexResult = 0
intRow = 3
Do While strTableResult(intIndexResult, 0) <> ""
ActiveSheet.Cells(intRow, 1).Value = strTableResult(intIndexResult, 0)
ActiveSheet.Cells(intRow, 2).Value = strTableResult(intIndexResult, 1)
ActiveSheet.Cells(intRow, 3).Value = strTableResult(intIndexResult, 2)
ActiveSheet.Cells(intRow, 4).Value = strTableResult(intIndexResult, 3)
ActiveSheet.Cells(intRow, 5).Value = strTableResult(intIndexResult, 4)
ActiveSheet.Cells(intRow, 6).Value = strTableResult(intIndexResult, 5)
ActiveSheet.Cells(intRow, 7).Value = strTableResult(intIndexResult, 6)
ActiveSheet.Cells(intRow, 8).Value = strTableResult(intIndexResult, 7)
ActiveSheet.Cells(intRow, 9).Value = strTableResult(intIndexResult, 8)
ActiveSheet.Cells(intRow, 10).Value = strTableResult(intIndexResult, 9)
ActiveSheet.Cells(intRow, 11).Value = strTableResult(intIndexResult, 10)
ActiveSheet.Cells(intRow, 12).Value = strTableResult(intIndexResult, 11)
ActiveSheet.Cells(intRow, 13).Value = strTableResult(intIndexResult, 12)
ActiveSheet.Cells(intRow, 14).Value = strTableResult(intIndexResult, 13)
ActiveSheet.Cells(intRow, 15).Value = strTableResult(intIndexResult, 14)
ActiveSheet.Cells(intRow, 16).Value = strTableResult(intIndexResult, 15)
ActiveSheet.Cells(intRow, 17).Value = strTableResult(intIndexResult, 16)
ActiveSheet.Cells(intRow, 18).Value = strTableResult(intIndexResult, 17)
ActiveSheet.Cells(intRow, 19).Value = strTableResult(intIndexResult, 18)
ActiveSheet.Cells(intRow, 20).Value = strTableResult(intIndexResult, 19)
ActiveSheet.Cells(intRow, 21).Value = strTableResult(intIndexResult, 20)
ActiveSheet.Cells(intRow, 22).Value = strTableResult(intIndexResult, 21)
ActiveSheet.Cells(intRow, 23).Value = strTableResult(intIndexResult, 22)
ActiveSheet.Cells(intRow, 24).Value = strTableResult(intIndexResult, 23)
intIndexResult = intIndexResult + 1
intRow = intRow + 1
Loop
End Sub
Bookmarks