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".

That is the side effect of doing a Find or a Find/Replace. Any settings you
change in code will persist and there is no way around it. The other problem
is that you can not read the settings in advance of changing them so that you
can put them back when you are done. The only thing you can do is to put back
settings that are not bound to cause a problem such as replace blank with
blank or such...
--
HTH...

Jim Thomlinson


"downwitch" wrote:

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".

I understand that the settings persist--it's an annoyance I can work
around.

My question is, is there something I can do on the VBA side to blow
them away before I start searching? There isn't even an analogous
argument in the VBA .Replace method to the Sheet/Workbook setting (or
the Search by or Look in settings, for that matter), since the method
is supposed to be limitable to a range...

Jim Thomlinson wrote:
That is the side effect of doing a Find or a Find/Replace. Any settings you
change in code will persist and there is no way around it. The other problem
is that you can not read the settings in advance of changing them so that you
can put them back when you are done. The only thing you can do is to put back
settings that are not bound to cause a problem such as replace blank with
blank or such...
--
HTH...

Jim Thomlinson


"downwitch" wrote:

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".

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".

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".