Comparing Two Ranges in Two different Workbooks

[seebnova]

Hi,
Hope you can help me.
I have a range in Workbook that I need to compare to another range in different workbook. If any cell not found I need to MegBox the offending cell and terminate the macro.

I have this but I got error message.

Sub Check()

'Dim Range1 As Range // not sure if I need to declare them
'Dim Range2 As Range // but when I do, I get the error message

Workbooks(Home1 & ".xls").Activate
Range1 = Sheets("Sheet1").Range("c3:c21")
Workbooks(Home2 & ".xls").Activate
Range2 = Sheets("Sheet1").Range("c11:c60")

For Each cell In Range1

If Not Range2.Find(cell.Value, LookIn:=xlValues) Is Nothing Then
MsgBox "This no where to find.", vbCritical, "Error!"
Stop
'If Not IsError(Application.Match(Cell.Value, Range2, 0)) Then
'// this also do not work

End If
Next

End Sub

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> Rng1 <SPAN style="color:#00007F">As</SPAN> Range, Rng2 <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#007F00">' Must use Set with Objects</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng1 = Workbooks("Book1").Sheets("Sheet1").Range("A1:A4")
<SPAN style="color:#00007F">Set</SPAN> Rng2 = Workbooks("Book2").Sheets("Sheet1").Range("A1:A10")
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Rng1
    <SPAN style="color:#00007F">If</SPAN> Rng2.Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Item " & c.Value & " on row " & c.Row & " has no match."
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">Set</SPAN> Rng1 = <SPAN style="color:#00007F">Nothing</SPAN>
Set Rng2 = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Try the above.

 

[seebnova]

Thanks alot, it worked magically!!