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

"watermt" wrote:

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?

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

"watermt" wrote:

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?

I keep getting a complie error when I run themacro.

"Mike H" wrote:

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

"watermt" wrote:

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?

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

Sub CommentFixer()
Dim c As Comment
Set r = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTyp eComments)
For Each rr In r
rr.Comment.Text Replace(rr.Comment.Text, "qwerty", "new")
Next
End Sub
--
Gary''s Student - gsnu200903


"watermt" wrote:

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?

Mike, Gary,
that makes sense now, i did not read comment not as 'Excel comment'.... lol

-kc

"watermt" wrote:

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?