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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Stefi" wrote:

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 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
'<<============

---
Regards,
Norman


"Stefi" wrote in message
...
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

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

"Stefi" wrote:

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

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

Regards,
Stefi


€žchijanzen€� ezt Ã*rta:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Stefi" wrote:

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

Thank you, Norman and Herbert, both solution was new to me, both works
perfectly!

Regards,
Stefi


€žHerbert€� ezt Ã*rta:

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

"Stefi" wrote:

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