Previous Topic Next Topic

Compare sheet 1 & Sheet 2 pull any items that

Closed
gregs - Mar 7, 2011 at 03:00 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 7, 2011 at 09:38 PM
Hello,

I saw this posted earlier... did you ever respond to this?

+++
Your macro has been a great help to identify the matching records- thank you. I have another situation in which I'm trying to compare the same data between sheet 1 and sheet 2, but I want sheet 3 to pull any items are are NOT matches. How can I change this macro to copy the non-matches to sheet 3?

I would like to compare a certain # of characters in sheet 1 and sheet 2 (for example - compare first 5 charaters in sheet1 to sheet 2 if no mathces found - populate on sheet 3.

Thanks

Greg



Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 7, 2011 at 09:38 PM
quick reply see whether this works. if not post a very small extract of sheet1. 2 and what you expect in sheet 3 

Sub test()
Dim rng As Range, c As Range, cfind As Range
On Error Resume Next
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sheet2")
Set cfind = .Columns("A:A").Cells.Find _
    (what:=c.Value, lookat:=xlWhole)
If NOT cfind Is Nothing Then GoTo line1
cfind.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End With 'sheet 2
line1:
Next c
application.cutcopymode=false 
End With 'sheet 1


End Sub
0