Microsoft Knowledge Base

WINDOWS   | MACINTOSH

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.

'------------------------------------------------------------------------

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

'------------------------------------------------------------------------

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.

REFERENCES

Microsoft Excel 5.0 Visual Basic Help "Excel 5 Visual Basic for Applications Reference," pages 552-553, 252-253 "Microsoft Excel Visual Basic User's Guide," page 100

To find the Replace method in Help, choose the search button in Programming with Visual Basic Help and type:

   Replace Method

   Find Method