ExcelBanter - Find and replace Comments

If you're using xl2k or higher, you can use:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim FindWhat As String
Dim WithWhat As String
Dim lArea As Long

FindWhat = "asdf"
WithWhat = "QWER"

Do
Set FoundCell = ActiveSheet.Cells.Find(What:=FindWhat, _
After:=ActiveCell, _
LookIn:=xlComments, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.Comment.Text _
Replace(expression:=FoundCell.Comment.Text, _
Find:=FindWhat, Replace:=WithWhat, Start:=1, _
Count:=-1, compa=vbTextCompare)

'from Debra Dalgleish's site (from Dana DeLouis' code)
'http://contextures.com/xlcomments03.html#Resize
With FoundCell.Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With

End If
Loop

End Sub

=====
If you're using xl97 or below, you'll have to make changes--there is no
Replace() function--it was added in xl2k.

It can be replaced with application.substitute(), but that's case sensitive, so
you'll have to be more careful and match case--in the .find, too.

Deba wrote:

I have replace comments I did in a spreadsheet, unfortunatly it is
about 800 of them . I can find them easily with find function -
comments but then the replace doesn't work for comments,only for
formulas. Is there a way round this? I might have to also resize the
box to a bigger size. Any suggestions I would be very grateful!!

--

Dave Peterson