comparision

[jackgn]

Hi all,

the below code compare the values from sheet1 and sheet2 and display the result is sheet3 which is present in sheet1 and sheet2, actually i need a code where it should compare the value from sheet1 and if it is not present in sheet2 then the result sholuld present in sheet3 can any one help in this.
Option Explicit
Sub Comparew2w1()
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim c As Range, FR As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
Set w3 = Worksheets("Sheet3")
w3.Columns(1).ClearContents
NR = 0
For Each c In w2.Range("A1", w2.Range("A" & Rows.Count).End(xlUp))
FR = 0
On Error Resume Next
FR = Application.Match(c, w1.Columns(1), 0)
On Error GoTo 0
If FR > 0 Then
NR = NR + 1
w3.Cells(NR, 1).Value = c
End If
Next c
w3.Columns(1).AutoFit
w3.Activate
End Sub

</PRE>

 

[Sandeep Warrier]

Hi

How about...

(Untested!)

Sub Test()
    Dim Rng1 As Range, Rng2 As Range, rCell As Range
    Dim WS As Worksheet
    Set Rng1 = Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row)
    Set Rng2 = Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Row)
    Set WS = Sheets("Sheet3")
    With WS
        For Each rCell In Rng1
            If Not IsNumeric(Application.Match(rCell.Value, Rng2, 0)) Then
                .Range("A" & .Rows.Count).End(xlUp).Offset(1).Value = rCell.Value
            End If
        Next rCell
    End With
End Sub

 

[jackgn]

Hi sandeep,

awesome thx for your timely help
Regards,
Jackgn

 

[jackgn]

Hi, Sorry actually it is exactly comparing the value like sheet1 A1 cell with sheet2 A cell, then it is extracting if the same value is present in A2 cell it is not comparing, can you please help me to compare the complete sheet1 with sheet2 from A1 to A65536.
Thanks
Jackgn

 

[jackgn]

sorry for the above post,
Hi,
thx for the above macro, it compare the value from sheet1 and if it is not present in sheet2 then the result present in sheet3 also i need a macro along with this where it should compare vise-versa also..,
i can paste the sheet2 value in sheet1 to get the result, but i need a macro to compare both the sheets. can any one help me on this, thx in advace.