Comments - using find and replace

Posted by Dave on November 21, 2001 7:30 AM

Anyone know a way of using the replace function for text in comments. I have 160 comments in a worksheet and I want to replace certain words (names etc) in all of them.

Posted by Juan Pablo on November 21, 2001 8:29 AM

Try this code. It'll search every cell in the activesheet with comments, and replace the string "Text" with "Proof "

Sub TestIt()
Dim Rng As Range
Dim Cll As Range

Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
For Each Cll In Rng.Cells
Cll.Comment.Text Application.Substitute(Cll.Comment.Text, "Text", "Proof ")
Next Cll
End Sub

Juan Pablo

Posted by Dave on November 21, 2001 8:46 AM

Worked perfectly...thanks
BUT... is there any non vba method

Posted by Juan Pablo on November 21, 2001 8:50 AM

Not that i know of (NT)

Posted by Dave on November 22, 2001 12:33 AM

Re: Not that i know of (NT)

I have found this Excel add in called PUP2000 which has loads of useful features such as find and replace in comments...available here http://j-walk.com/ss/pup/pup2000/trial.htm