PDA

View Full Version : Solved: Tricky copying to a new Sheet - Urgent Help Needed Please! Thanks!!!



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