Slow VBA/Macro - Need a more efficient code

[Kabasauls]

Hello,


Situation: Accounting database that needs to be matched based on the last two columns. Any unmatched rows are manually fixed.


[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]Type[/TD]
[TD="align: center"]Nbr[/TD]
[TD="align: center"]Post[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Auto Rev[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Nbr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Nbr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Create User[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Balance[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Amount[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]Amount[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]AP[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]65465[/TD]
[TD="align: center"]65464[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]4-26[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]sdfsdfs[/TD]
[TD="align: center"]asdfd[/TD]
[TD][TABLE="class: cms_table, width: 54"]
<tbody>[TR="class: outer_border"]
[TD="width: 54, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: cms_table, width: 83"]
<tbody>[TR="class: outer_border"]
[TD="width: 83, align: center"]43053[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]AP[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]65481[/TD]
[TD="align: center"]21548[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]4-12[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]sdfsdf[/TD]
[TD="align: center"]adfas[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]54152[/TD]
[/TR]
[TR]
[TD="align: center"]AP[/TD]
[TD="align: center"]VO[/TD]
[TD="align: center"]46481[/TD]
[TD="align: center"]54123[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]5-3[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]asdfasd[/TD]
[TD="align: center"]adsfa[/TD]
[TD="align: center"]43053[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]


Logic: When the non-zero amount on column K matches the one in column L, the macro adds "Ok" to the side of the respective cells's row. The macro skips the already Okay-ed cells and only compares the missing "Ok" values. Once the macro attempts to match all the values, a filter is used to show only non-okay cells. These cells are the unmatched ones.


Issue: The macro works, but it takes about 27 minutes to complete with a database of 15000+ rows. I have tried to make the macro more efficient, but I have reached my limit with my current knowledge in excel.

I have read that instead of using a loop, a faster way to accomplish the task is to put the value in an array, and then transfer the array to the worksheet.

I don't want to lose the dynamic aspect of my current macro, where it adapts to the new database's number of rows. Can any one point my in the right direction?

Any help will be greatly appreciated.

Code:

Private Sub Scan()
Dim Rows1 As Long
Dim Rows2 As Long
Dim lastRow As Long
Dim i As Long
Dim m As Long
i = 1
m = 1
 
'Find number of rows with data
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
'Scan first column
For i = 2 To lastRow
    For m = 2 To lastRow
    If Cells(i, 11).Value <> 0 And Cells(m, 12).Value <> 0 Then
        'Check for a blank cell
            If (Cells(i, 13).Value = "") And (Cells(m, 13).Value = "") Then
            'looks for a match
                If Cells(i, 11).Value = Cells(m, 12).Value Then
                    Cells(i, 13).Value = "Ok"
                    Cells(m, 13).Value = "Ok"
                End If
            End If
    End If
    Next m
Next i


End Sub

 

[iliace]

Iliace,

Once I added your modified code, the discrepancy was fixed. I am getting the result that I expected.

This is exactly what I needed. Thank you for your help!

Do you understand how the array thing works?

What is the final execution time?

 

[Kabasauls]

Yes, I understand the code and the edited logic as well. It makes sense.

I do not know how to measure the time through excel, but It takes about a minute to go through the 15000+ rows. Much better than before. Thanks, again.