Hi Forum,
I have written a piece of code that does reconciliation:
The first part checks between columns:
The second part checks for duplicates in a Column (A in this example)
Works absolutely fine on upto 100k Rows, then simply freezes on anything bigger. Is the an optimal way to write this? Should I be using a scripting dictionary for the reconciliation too? Ive been off VBA for a while now and I am pretty rusty! Thanks for reading and helping.
I have written a piece of code that does reconciliation:
The first part checks between columns:
Code:
Sub AutoRecon()
Worksheets("Main_Recon").Select
Dim i As Long, _
LRa As Long, _
LRb As Long, _
rowx As Long
LRa = Range("A" & Rows.Count).End(xlUp).Row
LRb = Range("G" & Rows.Count).End(xlUp).Row
rowx = 2
Application.ScreenUpdating = False
For i = 2 To LRa
If Range("A" & i).Errors.Item(xlNumberAsText).Value = True Then
Range("A" & i).Value = "N" & Range("A" & i).Value
rowx = rowx + 1
End If
Next i
rowx = 2
For i = 2 To LRb
If Range("G" & i).Errors.Item(xlNumberAsText).Value = True Then
Range("G" & i).Value = "N" & Range("G" & i).Value
rowx = rowx + 1
End If
Next i
rowx = 2
For i = 2 To LRa
If IsError(Application.Match(Range("A" & i).Value, Range("G2:G" & LRb), 0)) Then
Range("O" & rowx).Value = Range("A" & i).Value
rowx = rowx + 1
End If
Next i
rowx = 2
For i = 2 To LRb
If IsError(Application.Match(Range("G" & i).Value, Range("A2:A" & LRa), 0)) Then
Range("S" & rowx).Value = Range("G" & i).Value
rowx = rowx + 1
End If
Next i
Application.ScreenUpdating = True
End Sub
The second part checks for duplicates in a Column (A in this example)
Code:
Sub CopyDupsToR()Dim x, i&, j&, k, s$
Dim LR As Long
Worksheets("Main_Recon").Activate
LR = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
x = Sheets("Main_Recon").Range("A1:A" & LR)
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To LR
s = 1
For j = 1 To UBound(x, 2)
s = s & "~" & x(i, j)
Next j
If .Exists(s) Then
.Item(s) = 2 & Mid(.Item(s), 2)
Else
.Item(s) = s
End If
Next i
For Each k In .keys
If Val(.Item(k)) = 1 Then
.Remove k
Else
.Item(k) = Split(Mid(.Item(k), 3), "~")
End If
Next k
If .Count > 0 Then Sheets("RECONCILE").Range("M2").Resize(.Count, UBound(x, 2)).Value = Application.Index(.items, 0, 0)
End With
Application.ScreenUpdating = True
Worksheets("RECONCILE").Range("M1").Value = "Duplicates Left Side"
If Worksheets("RECONCILE").Range("M2") = "" Then
Worksheets("RECONCILE").Range("M2").Value = "NO RECORDS"
End If
End Sub
Works absolutely fine on upto 100k Rows, then simply freezes on anything bigger. Is the an optimal way to write this? Should I be using a scripting dictionary for the reconciliation too? Ive been off VBA for a while now and I am pretty rusty! Thanks for reading and helping.