Find/Replace VBA Risk

[HerryMarkowitz]

Greetings,
There is a big risk for Find/Replace macro.
Let me explain how;

1- Open an excel file.
2- Be sure that you have two sheets in this file, first one is Sheet1 and second one is Sheet2.
3- Enter Apple into Sheet1 A1 cell.
4- Enter Apple into Sheet2 A1 cell.
5- Press Ctrl + F in oder to open find and replace dialog box.
6- Enter Apple into find and replace dialog box, select ThisWorkbook instead of ThisSheet, and click Find All button. (You did this because you want to see all Apple values in the workbook.) (Everything is okey until now) (Close find and replace dialog box now.)
7- Now, you want to replace Apple value to Orange by using following code only in the Sheet1.

Please Login or Register  to view this content.

But code above replaces Apple values to Orange values in all sheets!!! This is the very dangerous thing!
Any idea???

Reason;
Find and replace dialog box DO NOT reset itself!!!

[alansidman]

Herry;
Yes this is a known bug. You will need to clear the dialogue box before running new searches. Look at this link and it will give you some additional code to add.

http://stackoverflow.com/questions/2...box-parameters

[shg]

It's a bug of long standing. Do a dummy Find first:

Please Login or Register  to view this content.

You can also use that dummy Find to set the states of the persistent arguments LookAt, SearchOrder, and MatchByte.

[Leith Ross]

Hello HerryMarkowitz,

If you read the VBA help files, you will find this information. It will tell you exactly which arguments you always need to set in both find and replace to avoid this problem.

The reason is both the Find and Replace functions in VBA use the Windows System Find and Replace dialog. This dialog does not reset to make it easier for users to modify the search or replace.

[HerryMarkowitz]

Hi Alan,
Thanks for the answer.
But link you adviced does not mention how to change ThisWorkbook selection to ThisSheet selection in the find and replace dialog box.
No mention because there is no way.
http://stackoverflow.com/questions/2...box-parameters
FYI.

[shg]

Harry, did you read post#3?

[HerryMarkowitz]

Harry, did you read post#3?

Yes I am.
And I made simplier code thanks to your post;

Please Login or Register  to view this content.

But, I am waiting if any other opinions exist or not

[Leith Ross]

For those who are interested, here are the links to the online VBA Help for both the Find and Replace methods...

Range.Find Method (Excel)

Range.Replace Method (Excel)

[HerryMarkowitz]

Thanks everybody.