Microsoft KB Archive/125180

= Microsoft Knowledge Base =

XL: No Alert When Replace Method Fails
Last reviewed: March 27, 1997

Article ID: Q125180

5.00 7.00 | 5.00

WINDOWS  | MACINTOSH kbdocerr

The information in this article applies to:


 * Microsoft Excel for Windows, version 5.0
 * Microsoft Excel for the Macintosh, version 5.0
 * Microsoft Excel for Windows 95, version 7.0

SYMPTOMS
In Microsoft Excel, the Replace method, which finds and replaces specified text strings in a range, does not behave consistently with what is stated in Visual Basic Help. It will not alert you if no match is found.

The Visual Basic code example shown below demonstrates one way to work around this behavior.

RESOLUTION
To see if a specified text string is found, use the Find method. If the Find method is unsuccessful in finding the text string, it will return the keyword Nothing. It is possible to execute the Find method and compare its results with the keyword, Nothing.

MORE INFORMATION
Visual Basic Help states in the Remarks section that the Replace method will return the TRUE logical value if the specified text string is found. In reality, the Replace will also returns TRUE even if the specified text string is not found. This can cause problems when knowing if any text was actually replaced is critical.

Visual Basic Code Example
Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

To use the FindIt subroutine, select any range in a worksheet. Then, choose Macro from the Tools menu, select FindIt from the list of macro names, and choose Run.

' Option Explicit

Sub FindIt 'Dimension "R" for later use. Dim R As Range 'Sets range R to the current selection. Set R = Selection 'Checks to see if the text is found. We're   'looking in formulas, looking in any part of    'the cell, searching by rows, and it's not 'case sensitive. "Old" will not be found 'if it's a result of a formula or a note. If R.Find("old") Is Nothing Then 'Display a message box to warn us that the 'text was not found. MsgBox "the text 'old' was not found!" Else 'If the text is found, replace all occurrence 'of it. The search is not case sensitive, 'We're searching by rows, and we're not 'matching entire cell contents. R.Replace What:="old", Replacement:="new" End If End Sub

' For example, if you place "old" in cell $A$1 and leave $A$2 blank on a worksheet and select the range $A$1:$A$2, and you then run the FindIt macro, the contents of $A$1 should change to "new". However, if you immediately run the FindIt macro again with the same range selected, the message, "the text 'old' was not found!" should appear. This is because $A$2 no longer contains the text, "old".

STATUS
Microsoft has confirmed this to be a problem in the products listed above. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.