Find and replace



Thank you so much for all the videos you put on youtube, it's been great help!

Quick question, is there a way to find and replace in comments?




Selected Answer

You're very welcome! :)

This is an interesting question and it's actually pretty easy, but it requires a macro.

Here is a small macro that I made that had 2 pop-up windows, one for the value to search for and 1 for the value to use as a replacement. Once it runs, it will show a window with a count of how many values were replaced.

Sub ReplaceComments()

Dim eComment As Comment
Dim eSheet As Worksheet
Dim valueFind As String
Dim valueReplace As String
Dim commentText As String
Dim numberReplaced As Long

numberReplaced = 0

valueFind = InputBox("Value to find:")

valueReplace = InputBox("New value to replace old value with:")

For Each eSheet In Sheets

    For Each eComment In eSheet.Comments

        commentText = eComment.Text

        If InStr(commentText, valueFind) <> 0 Then

            commentText = Application.WorksheetFunction. _
            Substitute(commentText, valueFind, valueReplace)
            eComment.Text Text:=commentText

            numberReplaced = numberReplaced + 1

        End If



If numberReplaced > 0 Then
    MsgBox numberReplaced & " replacements made."
    MsgBox "Nothing found to replace."
End If

End Sub

I just made this but only tested it a little bit.

Let me know how it works :)



Thank you so much again for your quick answer.  I know very little about VBA, so I replaced "Value to find:" with "ABC" and "New value to replace old value with:" with "DEF" with the intention of replacing "ABC" with "DEF" in the comments.  When I run it, a box with ABC comes up and asks me "OK" or "Cancel", so I click "OK", then another box appears with EDF and asks "OK" or "Cancel", again I click "OK", then a box appears with "1 replacements made", I click "OK" and check the comments, "ABC" is still there, did I do something terribly wrong?  :)

Thanks againe
Owen (rep: 2) Apr 30, '19 at 9:12 am
If you want to hardcode the values, you have to replace the entire function InputBox("Value to find:") with something like "ABC" and then the same for the next line. It sounds like you just replaced the text wtihin the original quotes.
don (rep: 1950) Apr 30, '19 at 9:30 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login