Hello
I modified this code but it shows a problem . when run from the first time will clear the data and when press second will show data
the result should be in sheet2 based on sheet1 it will clear data and brings again from sheet1 this is what happens now but should brings data every time when run from the first time .
I modified this code but it shows a problem . when run from the first time will clear the data and when press second will show data
the result should be in sheet2 based on sheet1 it will clear data and brings again from sheet1 this is what happens now but should brings data every time when run from the first time .
VBA Code:
Sub NewEntry_v2()
Dim OldDict As Object, arrIn As Variant, arrOld As Variant, arrNew() As Variant, a As Long, r As Long
Set OldDict = CreateObject("Scripting.Dictionary")
arrIn = Sheet1.Range("A2", Sheet1.Range("D" & Rows.Count).End(xlUp)).Value
arrOld = Sheet2.Range("A2", Sheet2.Range("D" & Rows.Count).End(xlUp)).Value
With OldDict
.CompareMode = vbTextCompare
For a = 1 To UBound(arrOld)
If Not .Exists(arrOld(a, 1)) Then OldDict.Add arrOld(a,1), arrOld(a, 1)
Next a
ReDim arrNew(1 To UBound(arrIn), 1 To 4)
For a = 1 To UBound(arrIn)
If Not .Exists(arrIn(a, 1)) Then
r = r + 1
arrNew(r, 1) = arrIn(a, 1)
arrNew(r, 2) = arrIn(a, 2)
arrNew(r, 3) = arrIn(a, 3)
arrNew(r, 4) = arrIn(a, 4)
End If
Next a
End With
Sheet2.Range("A2").Resize(UBound(arrIn), 4).Value = arrNew
OldDict.RemoveAll
r = 0
arrIn = Sheet2.Range("A2", Sheet2.Range("D" & Rows.Count).End(xlUp)).Value
arrOld = Sheet1.Range("A2", Sheet1.Range("D" & Rows.Count).End(xlUp)).Value
With OldDict
For a = 1 To UBound(arrOld)
If .Exists(arrOld(a, 1)) Then OldDict.Add arrOld(a, 1), arrOld(a, 1)
Next a
ReDim arrNew(1 To UBound(arrIn), 1 To 4)
For a = 1 To UBound(arrIn)
If .Exists(arrIn(a, 1)) Then
r = r + 1
arrNew(r, 1) = arrIn(a, 1)
arrNew(r, 2) = arrIn(a, 2)
arrNew(r, 3) = arrIn(a, 3)
arrNew(r, 4) = arrIn(a, 4)
End If
Next a
End With
Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(arrIn), 4).Value = arrNew
End Sub