itipu
11-28-2007, 08:13 AM
Dear all,
As in my example I wrote a little code to compare my sheets:
Sub test()
Dim r1 As Range, r2 As Range, rc As Range
Worksheets("Compare").Activate
Set r1 = Worksheets("Compare").Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Worksheets("Compare2").Activate
Set r2 = Worksheets("Compare2").Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
For Each rc In r1
If r2.Find(what:=rc, LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
'Copy rc to a ColumnA of new Sheet "Out", put "missing" in ColumnB of "Out"
rc = rc & "Missing"
Else
'Copy the whole row into Sheet "Out"
r2.Find(what:=rc, LookIn:=xlValues, lookat:=xlWhole).Value = r2.Find(what:=rc, LookIn:=xlValues, lookat:=xlWhole).Value & " Found"
End If
Next
End Sub
Currently it works in a test mode by adding either "Missing" or "Found"
Now I need to implement what is commented:
'Copy rc to a ColumnA of new Sheet "Out", put "missing" in ColumnB of "Out"
AND
'Copy the whole row into Sheet "Out"
Thanks a lot
Mike
As in my example I wrote a little code to compare my sheets:
Sub test()
Dim r1 As Range, r2 As Range, rc As Range
Worksheets("Compare").Activate
Set r1 = Worksheets("Compare").Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Worksheets("Compare2").Activate
Set r2 = Worksheets("Compare2").Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
For Each rc In r1
If r2.Find(what:=rc, LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
'Copy rc to a ColumnA of new Sheet "Out", put "missing" in ColumnB of "Out"
rc = rc & "Missing"
Else
'Copy the whole row into Sheet "Out"
r2.Find(what:=rc, LookIn:=xlValues, lookat:=xlWhole).Value = r2.Find(what:=rc, LookIn:=xlValues, lookat:=xlWhole).Value & " Found"
End If
Next
End Sub
Currently it works in a test mode by adding either "Missing" or "Found"
Now I need to implement what is commented:
'Copy rc to a ColumnA of new Sheet "Out", put "missing" in ColumnB of "Out"
AND
'Copy the whole row into Sheet "Out"
Thanks a lot
Mike