Find comment replace cell value

[magarians]

Hi. I've searched through previous posts, and it seems that none is
what I am looking for. Everyone else seems to want to find cells with
specific text in the comment and replace that text within the comments.
What I need is to find all cells with specific text in the comments,
then replace the cell values. "Find all cells with "xxx" in the
comments and change all of the cell values to "123". I'd do it one by
one but there are hundreds. None of my macros seem to work. Any
ideas?

Thanks.
Kind regards,

 

[magarians]

Actually, I just figured it out. If anyone is interested, my
successful macro is:

blic Sub findXYZ()

Dim myrange As Range
Dim rCell As Range
Const sStr As String = "XYZ"
Dim theCmt As Comment

Sheets("mysheet").Activate
Set myrange = Selection
For Each rCell In myrange
Set theCmt = rCell.Comment
If Not theCmt Is Nothing Then
If InStr(1, theCmt.Text, sStr, vbTextCompare) > 0 Then
rCell.Value = 123
End If
End If
Next rCell


End Sub

 

[Guest]

Try this:

Select the range to be impacted

From the Excel main menu:
<edit><find>
Find what: xxx
Click the [options] button
Look in: Comments
Click the [Find all] button

While the list of matching cells is displayed....
Hold down the [Ctrl] key and press A
(that will select all of the matching cells)

If you need those cells to all have the same value....eg 123:
Type 123
Hold down the [Ctrl] key and press [enter]
(instead of just pressing [enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Nick Hodge]

Something like this will do (Lightly tested)

Sub FindTextInComment()
Dim txtToFind As String
Dim cmt As Comment
Dim res As Double
txtToFind = InputBox("Enter text to find in comments")
For Each cmt In ActiveSheet.Comments
res = InStr(1, cmt.Text, txtToFind, 1)
If res > 0 Then
cmt.Parent.Value = "123"
End If
Next cmt
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)