Finding outside set range

[Sue]

I am using Excel 2000 and have the following code:
With Worksheets("Data Lists").Range(WorkRange)
Set MyEdit = Cells.Find(AccCode, , xlValues, xlWhole, xlByColumns)
End With
This does find the AccCode in the WorkRange BUT it also finds the AccCode
outside the WorkRange. Why? How can I stop that?
Sue

 

[mikerickson]

Cells (without the dot) refers to the whole ActiveSheet. To restrict it to WorkRange you need to add the dot

With Worksheets("Data Lists").Range(WorkRange)
    Set MyEdit = .Cells.Find(AccCode, , xlValues, xlWhole, xlByColumns)
End With

Or you could omit it entirely

With Worksheets("Data Lists").Range(WorkRange)
    Set MyEdit = .Find(AccCode, , xlValues, xlWhole, xlByColumns)
End With

 

[Bob Bridges]

You're probably thinking that the With header limits everything that happens
inside it to the Range you specified. But that isn't how it works.

The With statement makes available a sort of default parent object inside
that section - but only AVAILABLE. So your With Worksheets("Data
Lists").Range(WorkRange) allows you, inside that section, to refer to
".Cells.Count" (notice the leading dot), and it'll tell you how many cells
are in Worksheets("Data Lists").Range(WorkRange).Cells.Count, or to
".Font.Bold" and it'll give you True or False depending on whether
Worksheets("Data Lists").Range(WorkRange).Font.Bold is True or False. The
leading dot specifies that the parent object for that term is to be whatever
you specified in the With statement.

But you never used a leading dot in any term in that section; you said "Set
MyEdit = Cells.Find(AccCode, , xlValues, xlWhole, xlByColumns)". Therefore
it did the Find inside the range defined by Cells with no leading dot; Access
assumes the default parent object for Cells is ActiveSheet, I believe, so it
looked in the whole active worksheet. What you want, I take it, is "Set
MyEdit = .Find(AccCode, , xlValues, xlWhole, xlByColumns)", where ".Find"
means "Worksheets("Data Lists").Range(WorkRange).Find". You see?