I am using a similiar subroutine finding data in one sheet that
matches that in another sheet.
I find it very confusing and after lots of trial and error get the
routines to work but don't exactly understand why.
If I don't have line 6(activate) prior to setting range DRange
I get an error.
If I don't have line 8 (activate) prior to the For Each c line
I get an error.
yet lines 2, 3, 5 seem to work fine without first
activating the sheet.
Yet once I get into the For/If section (lines 9 - 18) I don't seem to
have to use the .Activate command. see line 10 is commented out.
Why is that?
also is there a difference between Worksheets and Sheets?
sometimes I think they are the same, other times I am not so sure.

Also what is the difference between .activate and .select.
I notice using hidden sheets activate works and select does not.
In the routine below, excel ends up showing test2 on the screen,
although it has never been selected.
Thanks
Don



1 Sub test()
2 Worksheets("test3").Columns(2).ClearContents
3 Worksheets("test3").Cells(11, 1).ClearContents
4
5 Worksheets("test3").Cells(11, 1) = Worksheets("test2").Cells(1, 1)
6 Sheets("test3").Activate
7 Set DRange = Worksheets("test3").Range(Cells(1, "a"), Cells(7,
"a"))
8 Sheets("test2").Activate
9 For Each c In Worksheets("test2").Range(Cells(1, "a"), Cells(7,
"a"))
10 'Worksheets("test3").Activate
11 Set D = DRange.Find(c, , , xlWhole)
12
13 If Not D Is Nothing Then
14 'MsgBox D & " " & D.Row
15 Worksheets("test3").Cells(D.Row, 2) = c
16 Else
17 Worksheets("test3").Cells(10, 2) = c & " was not found"
18 End If
19 L: Next
20 End Sub