Range.Replace() problems in all Excel versions
Range.Replace() and supposedly Range.Find() may work incorrectly in a scenario we describe on the Excel for Developers forum. A workaround is: change Range.Value directly.
Although the title mentions only the Replace method, I assume the issue also relates to using the Range.Find() method because, obviously, finding is part of replacing.
Would you expect Range.Replace() to look through the whole sheet or even workbook? I wouldn’t. I expect the replacement only to occur on the cell(s) that the Range object contains. Michael Kaden created a topic on our forum to demonstrate that for a Range object pointing to a single cell, the Range.Replace() call works on the whole sheet.
While trying to find more information about the issue, I paid attention to the Remarks section of the method description on msdn:
The settings for LookAt, SearchOrder, MatchCase, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.
Needless to say, how important this remark is.
This remark also gave me an idea: I’ve looked in the Find dialog (you open it by pressing Ctrl+F) and found the setting highlighted in the screenshot below:
The rest is simple. You remember the story of Ethiopian children who taught themselves to use tablets, don’t you? In much the same fashion, I’ve found that the setting above does influence the way Range.Replace() works.
Although there’s some difference, the issue is reproducible in a COM add-in or VBA macro. You can find more details about my tests (including the Excel versions used) in my post on the Excel for Developers forum.
As to a workaround, don’t use Range.Replace() in this situation; replace the cell contents directly.