Replacing in Worksheets and Comments At the Same Time

by Allen Wyatt
(last updated May 28, 2016)

1

Dorothy's students are customizing a checkbook simulation workbook, replacing the default name with their own. For example, she wants them to search for "Jason Anderson" and replace the name with "Sue Smith." There are 22 occurrences of "Jason Anderson" in text boxes throughout eight worksheet tabs, and several more on the worksheets themselves. Dorothy wonders how she can Find and Replace all worksheet occurrences and all comment occurrences at once.

There is no way to do this without using a macro. The macro would need to look through each cell in the workbook, each comment, and each text box to see if changes needed to be made. The following is an example of how this can be done:

Sub ReplaceNames()
    Dim sDefaultName As String
    Dim sNewName As String
    Dim sht As Worksheet
    Dim cmt As Comment
    Dim wks As Worksheet
    Dim sCmt As String
    Dim shp As Shape

    sDefaultName = "Jason Anderson"
    sNewName = InputBox("Enter your first and last name.", _
      "Name Replacement")

    ' Replace cell text
    For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace what:=sDefaultName, _
          Replacement:=sNewName, LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
    Next sht

    ' Replace comment text
    For Each wks In ActiveWorkbook.Worksheets
        For Each cmt In wks.Comments
            sCmt = cmt.Text
            If InStr(sCmt, sDefaultName) <> 0 Then
                sCmt = Application.WorksheetFunction. _
                  Substitute(sCmt, sDefaultName, sNewName)
                cmt.Text Text:=sCmt
            End If
        Next cmt
    Next wks
    Set wks = Nothing
    Set cmt = Nothing

    ' Replace text box text
    On Error Resume Next
    For Each wks In ActiveSheet.Parent.Worksheets
        For Each shp In wks.Shapes
            With shp.TextFrame.Characters
                .Text = Application.WorksheetFunction.Substitute( _
                  .Text, sDefaultName, sNewName)
            End With
        Next shp
    Next wks
End Sub

The macro prompts the user to enter a new name and then searches cells, comments, and text boxes to see if there are any occurrences of the default name ("Jason Anderson"). If so, then the appropriate replacement is made.

If you don't want to use macros to do the searching, perhaps a restructuring of the workbook would be helpful. For instance, you could create a "data input worksheet" in which common information—such as the person's name—is entered. In the other worksheets, the name from the data input worksheet is referenced using formulas. Thus, one change in the data input worksheet, and the name is changed in all the other worksheets.

Using this approach can work with comments, as well. You can select the comment or shape and, in the Formula bar, enter a reference formula such as this:

=MyName

This assumes that the user's name, on the data input worksheet, has been associated with the MyName named range. If there needs to be additional information in the comment or shape, then you could construct that text in a cell on the data input worksheet and then assign the contents of that cell to the comment or shape, just as noted for the MyName named range.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2614) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Replacing Multiple Spaces with Tabs

If you get a document or some text that has multiple consecutive spaces used to align information, you'll undoubtedly be ...

Discover More

Finding the Previous Occurrence

Using Word's Object Browser, it is very easy to move among different instances of what you want to search in your document. ...

Discover More

Creating a Directory

Need to create a directory from within a macro? You can do it using a single command line, as detailed in this tip.

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Find and Replace in Headers

Using Find and Replace is something quite routine in Excel, as it easily allows you to find and replace information in your ...

Discover More

Finding Cells Filled with a Particular Color

Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your version of ...

Discover More

Replacing Tildes at the Beginning of a Cell

Replacing a specific character (such as a tilde) seems a simple task, until you need to replace it only in a certain ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2016-05-28 08:03:12

Kevin

"You can select the comment or shape and, in the Formula bar, enter a reference formula such as this:
=MyName"
I don't think this is possible. Formulas cannot be entered in a comment.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.