why the code run from the second press button?

[Mussa]

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 .

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

 

[davesexcel]

Could you explain what the result should be when you run the code.

Possibly you are trying to keep items in sheet 2 that are not in sheet 1, then add new items from sheet1 to sheet2?

 

[Mussa]

should match based on column A

MS.xlsm
	A	B	C	D
1	NM	ID	JOB	PART
2	MM1-1	1-ID100	ADMIN1	PART1
3	SD-12	1-ID101	ADMIN2	PART2
4	MM1-2	1-ID102	ADMIN3	PART3
5	SD-13	1-ID103	ADMIN4	PART4
6	MM1-3	1-ID104	ADMIN5	PART5
7	SD-14	1-ID105	ADMIN6	PART6
8	SD-15	1-ID106	ADMIN7	PART7
sheet1

MS.xlsm
	A	B	C	D
1	NM	ID	JOB	PART
2	MM1-1	1-ID100	ADMIN1	PART11
3	SSD-15	1-ID1052	ADMIN7	PART7
4	SD-14	1-ID1051	ADMIN6	PART6
5	MM1-2	1-ID10211	ADMIN3	PART3
6	SD-13	1-ID1031	ADMIN4	PART4
7	SD-12	1-ID101	ADMIN2	PART222
8	MM1-3	1-ID1041	ADMIN5	PART5
sheet2

result based on column B with keep data are in sheet2 but are not existed in sheet1(data could be 6000 rows both two sheets )

MS.xlsm
	A	B	C	D
1	NM	ID	JOB	PART
2	MM1-1	1-ID100	ADMIN1	PART1
3	SD-12	1-ID101	ADMIN2	PART2
4	MM1-2	1-ID102	ADMIN3	PART3
5	SD-13	1-ID103	ADMIN4	PART4
6	MM1-3	1-ID104	ADMIN5	PART5
7	SD-14	1-ID105	ADMIN6	PART6
8	SD-15	1-ID106	ADMIN7	PART7
9	SSD-15	1-ID1052	ADMIN7	PART7
sheet2