Agfdhyk

I've been doing a tool which is to compare 2 workbooks. I don't have any problem in my code, is just that, if I have a maximum rows it took me more than 5hrs to process the data.

I need to compare the previous and latter file, if the previous file is not in the latter file then the rows are deleted. While, If I compare the latter to previous and there are rows that have been added in the latter file then the rows are added. The Data contains delimiter "¦" (e.g. 1315083¦Tamayo¦Juan¦Juan.Tamayo@xyz.com¦admin¦Requestor). When I split the data into "¦", the 1st and last array (1315083 and Requestor) will be used to compare the data. I used LCase in may condition to accept any casing.

Dim wbCopyT As Workbook

Set wbCopyT = Workbooks.Add



Dim arr As Variant

arr = wbCopyT.Sheets("Sheet3").Range("A1:A" & wbCopyT.Sheets("Sheet3").Range("A" & Rows.count).End(xlUp).Row).Value



Dim varr As Variant

varr = wbCopyT.Sheets("Sheet3").Range("B1:B" & wbCopyT.Sheets("Sheet3").Range("B" & Rows.count).End(xlUp).Row).Value



'compare data from previous to latter file

Dim x As Variant, y As Variant, match As Boolean

Dim splitVal1 As Variant, splitVal2 As Variant

Dim output1 As String, output2 As String



For Each x In arr

    match = False

    splitVal1 = Split(x, "¦")

    output1 = LCase(splitVal1(0)) & LCase(splitVal1(5))

    For Each y In varr

        splitVal2 = Split(y, "¦")

        output2 = LCase(splitVal2(0)) & LCase(splitVal2(5))

        If output1 = output2 Then

            wbCopyT.Sheets("Sheet3").Range("C" & wbCopyT.Sheets("Sheet3").Range("C" & Rows.count).End(xlUp).Row + 1) = x & "~No update/change"

            match = True

        End If

    Next y

    If Not match Then

        wbCopyT.Sheets("Sheet3").Range("C" & wbCopyT.Sheets("Sheet3").Range("C" & Rows.count).End(xlUp).Row + 1) = x & "~Deleted"

    End If

Next x



'compare data from latter to previous file

Dim a As Variant, b As Variant, match2 As Boolean

Dim splitVal3 As Variant, splitVal4 As Variant

Dim output3 As String, output4 As String



For Each a In varr

    match2 = False

    splitVal3 = Split(a, "¦")

    output3 = LCase(splitVal3(0)) & LCase(splitVal3(5))

    For Each b In arr

        splitVal4 = Split(b, "¦")

        output4 = LCase(splitVal4(0)) & LCase(splitVal4(5))

        If output3 = output4 Then

            wbCopyT.Sheets("Sheet3").Range("D" & wbCopyT.Sheets("Sheet3").Range("D" & Rows.count).End(xlUp).Row + 1) = a & "~No update/change"

            match2 = True

        End If

    Next b

    If Not match2 Then

        wbCopyT.Sheets("Sheet3").Range("D" & wbCopyT.Sheets("Sheet3").Range("D" & Rows.count).End(xlUp).Row + 1) = a & "~Added"

    End If

Next a

I need to limit the processing time but I don't know how to do that. Is there a problem with my code? THank you.