Finding a comment text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Is there a better way to find a certain comment text (say in a variable
named commtext) within a range than checking each cell in the range with an
If Cell.Comment.Text = commtext then
statement in a For each ... cycle?

Thanks,
Stefi
 
Hi Stefi:

Try

Dim comrng As Range, rng As Range
Set comrng = Cells.SpecialCells(xlCellTypeComments)
Const commtext = "???"
For Each rng In comrng
If rng.Comment.Text = commtext Then
MsgBox rng.Address(0, 0)
End If
Next
 
Hi Stefi,

Try limiting the search to cells with comments, e.g.:

'============>>
Public Sub AAA()
Dim rng As Range
Dim rCell As Range
Const sStr As String = "Hi" '<<== Your search string

Set rng = Range("A1:D100").SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
If InStr(1, rCell.Comment.Text, sStr, vbTextCompare) Then
'do something, e.g.:
MsgBox rCell.Address
End If
Next rCell
End If
End Sub
'<<============
 
Hello,

you could use the Comments object

Dim ws As Worksheet, c As Comment

Set ws = ActiveSheet

For Each c In ws.Comments
Debug.Print c.Text
Next c

Regards,
Herbert
 
Yes, it reduces the range to be searched to comrng (cells that a comment is
assigned to).
Thank you!

Regards,
Stefi


„chijanzen†ezt írta:
 
Thank you, Norman and Herbert, both solution was new to me, both works
perfectly!

Regards,
Stefi


„Herbert†ezt írta:
 
Back
Top