comparing and copying multiple rows on 2 sheets

**Miranda82** · 01-18-2007, 03:22 PM

I have been looking on here but I am still not understanding how to write the code. I have the concept but i don't know excel VBA enough.
Heres the problem.
MO view 'sheet 1
A G
M405250
M406230
M408250
M413480
M413690
M414260
M414280

RM 'sheet 2
A C
M405040 12/1/06
M405250 12/5/06
M406230 12/11/06
M408250 12/11/06
M413480 12/15/06
M413690 12/15/06
M414210 12/18/06
M414210 12/18/06
M414230 12/19/06

I need to compare Column A on both sheets and if they match copy column C on the corisponding line in "RM"sheet to Column G on "MO view"sheet. If there is no match it needs to be left blank. I would also like it to stop when it gets to the last line with data in "MO view"
Results would look like
MO view
A G
M405250 12/5/06
M406230 12/11/06
M408250 12/11/06
M413480 12/15/06
M413690 12/15/06
M414260
M414280

I am not sure how to do this and make sure it looks all the way through "RM" and returns a date or blank for each one.

Any code would be much appriciated.

**mrice** · 01-18-2007, 03:30 PM

This should do the trick

Sub Test()
Sheets("MO View").Activate
For N = 1 To Cells(65536, 1).End(xlUp).Row
If Application.CountIf(Sheets("RM").Columns(1), Cells(N, 1)) > 0 Then
Cells(N, 7) = Sheets("RM").Columns(1).Find(Cells(N, 1), , xlValues, xlWhole).Offset(0, 2)
End If
Next N
End Sub

**Miranda82** · 01-18-2007, 03:38 PM

Thanks that works but now it keeps restarting after it reaches the last line that has data. How do i make it stop?

**mrice** · 01-18-2007, 03:47 PM

Hmmmm... Didn't do that for me.

Have you put the code onto a module or onto the the tabs for one of the sheets. I sounds like you have somehow got it linked to a worksheet change event. Try creating a new module and putting it there.

Does this help?