Code VBA to find unique values between 2sheets.

[lotto009]

Dear all
How I edit code VBA to find unique values between 2sheets.
Step
1. I have value at Worksheets ("sheet2"). Range. ("J2: J300") this is input
2. Bring value to compare to reference-Worksheets ("sheet1"). Range ("R3: R500")
3. If a duplicate value with reference-Worksheets ("sheet1"). Range ("R3: R500") no need.
4. Not duplicate value with reference-Worksheets ("sheet1"). Range ("R3: R500") keep value in the reference sheet and bring output value to Worksheets ("sheet2"). Range. ("P2: P500")
because I need value only reference-Worksheets ("sheet1"). Range ("R3: R500")
Thank you I show to code att and file excel in my link

reference cheak.xlsm

drive.google.com

Sub Button1_Click()
    Dim Na As Long, Nc As Long, Ne As Long
    Dim i As Long
    Na = Worksheets("sheet1").Cells(Rows.Count, "R").End(xlUp).Row
    Nc = Worksheets("sheet2").Cells(Rows.Count, "J").End(xlUp).Row
    Ne = 1

    For i = 1 To Na
        Cells(Ne, "P").Value = Cells(i, "R").Value
        Ne = Ne + 1
    Next i
    For i = 1 To Na
        Cells(Ne, "P").Value = Cells(i, "J").Value
        Ne = Ne + 1
    Next i

    Range("P:P").RemoveDuplicates Columns:=1, Header:=xlNo
   
End Sub

 

[EXCEL MAX]

Try this:

Dim Na As Long, Nc As Long, Ne, i As Long
    Dim A
    
    Na = Worksheets("sheet1").Cells(Rows.Count, "J").End(xlUp).Row
    Nc = Worksheets("sheet2").Cells(Rows.Count, "R").End(xlUp).Row
    Ne = 2
    
    For i = 3 To Nc
        On Error Resume Next
        A = Sheets("sheet1").Range("J2:J" & Na).Find(Sheets("sheet2").Range("R" & i))
        If A Then
        Else
            Sheets("sheet1").Range("P" & Ne) = Sheets("sheet2").Range("R" & i)
            Ne = Ne + 1
        End If
    Next i

 

[lotto009]

Try this:

Dim Na As Long, Nc As Long, Ne, i As Long
    Dim A
   
    Na = Worksheets("sheet1").Cells(Rows.Count, "J").End(xlUp).Row
    Nc = Worksheets("sheet2").Cells(Rows.Count, "R").End(xlUp).Row
    Ne = 2
   
    For i = 3 To Nc
        On Error Resume Next
        A = Sheets("sheet1").Range("J2:J" & Na).Find(Sheets("sheet2").Range("R" & i))
        If A Then
        Else
            Sheets("sheet1").Range("P" & Ne) = Sheets("sheet2").Range("R" & i)
            Ne = Ne + 1
        End If
    Next i

Dear
As above not work kindly advisor
after edit

Sub Button1_Click()
   Dim Na As Long, Nc As Long, Ne, i As Long
   Dim A
    
    Na = Worksheets("sheet1").Cells(Rows.Count, "r").End(xlUp).Row 'refer
    Nc = Worksheets("sheet2").Cells(Rows.Count, "j").End(xlUp).Row 'input
    Ne = 2
    
    For i = 3 To Nc
        On Error Resume Next
        A = Sheets("sheet1").Range("r2:r" & Na).Find(Sheets("sheet2").Range("j" & i))
        If A Then
        Else
            Sheets("sheet2").Range("P" & Ne) = Sheets("sheet2").Range("j" & i)
            Ne = Ne + 1
        End If
    Next i

    
    
End Sub

 

[lotto009]

OHHH Thank you I can do is working
Br