Excel "Replace" is unreliable

Ron Coderre

MrExcel MVP

Joined

Jan 14, 2009

Messages

2,316

• Aug 5, 2011

• #2

Perhaps if you posted a example workbook at one of the free file sharing
sites, we'll see if we can spot the issue.

Some free filehosts that you might consider using:
MediaFire: http://www.mediafire.com/
FileFactory: http://www.filefactory.com/
FileSavr: http://www.filesavr.com/
FileDropper: http://www.filedropper.com/

 

Upvote 0

H

Hamburgler

Board Regular

Joined

May 16, 2009

Messages

148

• Aug 5, 2011

• #3

I am assuming that you are talking about the CTRL+H Find and Replace dialog, not the REPLACE worksheet function.

One possible explanation is that the settings in the OPTIONS part of the FIND AND REPLACE dialog have been changed, for example the "Match entire cell contents" box has been ticked. Excel remembers these preferences permanently when they are changed.

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,525

Office Version

1. 365

Platform

1. Windows

• Aug 6, 2011

• #4

Hamburgler said:

Excel remembers these preferences permanently when they are changed.

Click to expand...

To add to Hamburgler's statement above...

Excel remembers these preferences permanently when they are changed even if those preferences were changed inside via VB Replace method used inside a macro, event code or a UDF (yes, a UDF).

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,845

Office Version

1. 2010

Platform

1. Windows

• Aug 6, 2011

• #5

I believe LookIn, LookAt, MatchByte, and SearchOrder persist from the UI or a VBA Find to subsequent Finds. The other parameters do not.

MatchCase presists from the UI to subsequent UI Finds, but not to VBA.

More maliciously, Replace with workbook scope persists from the UI to VBA (it's not otherwise an option in VBA), and you have to do a dummy Find to correct the behavior.

 

Last edited: Aug 6, 2011

Upvote 0

D

davidam

Active Member

Joined

May 28, 2010

Messages

497

Office Version

1. 2021

Platform

1. Windows

• Aug 8, 2011

• #6

I am using the REPLACE worksheet function. I suspect that that the "remembering" is the issue as I do us lookat and lookin in some vba functions. In the last instance, the issue cleared up when I rebooted.
I will experiment with this in the near future.
Thanks to All.

 

Upvote 0

D

davidam

Active Member

Joined

May 28, 2010

Messages

497

Office Version

1. 2021

Platform

1. Windows

• Oct 29, 2011

• #7

Finally this issue is resolved for me. As Hamburgler suggested, all I had to do was check the options dialog on the Worksheet replace function and uncheck 'entire cell contents' . Yes, it does get re-checked by VBA functions that employ Lookat:=xlWhole (which, by the way, is absolutely essential when using VBA Find).

 

Upvote 0