Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find&Replace in Comments (Excel 2003)

    I have a large workbook with many commented cells. Some comments need to be corrected e.g. FIRR/I has to be FIRR/C. I see that I can only use "Find" for comments and not "Find & Replace". Does anybody know how to do a F&R in Cell Comments?

    Teunis

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find&Replace in Comments (Excel 2003)

    Andrew's Utilities 2, a free add-in includes the option to find and replace in comments.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find&Replace in Comments (Excel 2003)

    The following crude code will replace text in comments, but it removes all formatting too... It can easily be modified to loop through all worksheets, if needed.

    Sub ReplaceFIRR()
    ReplaceInComments "FIRR/I", "FIRR/C"
    End Sub

    Sub ReplaceInComments(strFind As String, strReplacement As String)
    Dim wsh As Worksheet
    Dim cmt As Comment
    Dim strComment As String
    Set wsh = ActiveSheet
    For Each cmt In wsh.Comments
    strComment = Replace(cmt.Text, strFind, strReplacement)
    cmt.Delete
    cmt.Parent.AddComment Text:=strComment
    Next cmt
    Set wsh = Nothing
    Set cmt = Nothing
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find&Replace in Comments (Excel 2003)

    Tony,

    How fast can you be! Just what I needed. Thanks.

    Regards, Teun

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find&Replace in Comments (Excel 2003)

    Hans,

    Thanks a lot for your effort, but I went for AndrewsUtilities since they include other goodies as well. And I thought I'd knew all utilities by now...

    Regards, Teun

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •