Edit All Comments

  • Thread starter Thread starter watermt
  • Start date Start date
W

watermt

I have a lot of comments in a worksheet and have been requested to replace
one word in each of the comments with another word. Is there a way to
replace all occurences of the word without opening each and every comment one
at a time?
 
There is a 'Replace All' button in 'Find and Replace' window (CTRL+H) right?
doesn't this work?

-kc
*Click YES if this helps
 
Hi,

Right click your sheet tab, view code and paste the code below in, Change
oldstring & new string for the text you want to find and replace

Sub EditComments()
oldstring = "Oldthing"
newstring = "Newthing"
Dim c As Comment
If ActiveSheet.Comments.Count > 0 Then
For Each c In ActiveSheet.Comments
If InStr(c.Text, oldstring) > 0 Then
newstring = WorksheetFunction.Substitute(c.Text, oldstring, newstring)
With Range(c.Parent.Address)
.ClearComments
.AddComment
.Comment.Text Text:=newstring
End With
End If
Next
End If
End Sub


Mike
 
Here is a small macro that changes "qwerty" to "new":

Sub CommentFixer()
Dim c As Comment
Set r = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeComments)
For Each rr In r
rr.Comment.Text Replace(rr.Comment.Text, "qwerty", "new")
Next
End Sub
 
Back
Top