This is what I was looking for. It does the trick. And it only has to
be run once! ;)

Thanks to you both.

On Aug 1, 12:10 pm, "Peter T" <peter_t@discussions wrote:
I always reset it -

On Error Resume Next
Set r = Cells.Find(What:="", _
LookIn:=xlFormulas, _
SearchOrder:=xlRows, _
LookAt:=xlPart, _
MatchCase:=False)
On Error GoTo 0

Reset the defaults

On Error Resume Next
Set r = Cells.Find(What:="", _
LookIn:=xlFormulas, _
SearchOrder:=xlRows, _
LookAt:=xlPart, _
MatchCase:=False)
On Error GoTo 0

Regards,
Peter T

"downwitch" wrote in message

ups.com...

Very odd behavior. I have a bit of standard VBA code that copies a
range, pastes its formulas to another range, selects that second
range, and then replaces a part of the formulas (so that I can have
two sets of ranges point at each other by templating one range only)
like so:

wks.Range("Rng1").Copy
wks.Range("Rng2").PasteSpecial xlPasteFormulas
wks.Range("Rng2").Replace What:="_1", Replacement:="_2",
LookAt:=xlPart

Works great, until...

...a user does a find/replace using the dialog box, chooses to search
within "Workbook" rather than "Sheet", at which point the .Replace
suddenly replaces _1 with _2 *everywhere*. VBA "remembers" Excel's
setting of where to search, despite the fact that I've called the
replace operation explicitly on the range. (Selecting the range in
code makes no difference, as usual.)

I've tried recording macros every which way to catch how Excel might
correct this, but no dice--seems like unless the user resets the
dialog, the VBA operation is doomed to fail overzealously.

I believe in Word there is a way to reinitialize that dialog. Does
this exist in Excel? Obviously, I can write my own little piece of
code that will find & replace just as effectively, but it seems to me
that this is the kind of thing a developer ought to be able to count
on "out of the (dialog!) box".