COUNTIF to count cells that have an attached comment

  • Thread starter Stonewall Rubberbow
  • Start date
S

Stonewall Rubberbow

Can I use COUNTIF (or anything) to count the number of cells in a range that
have a comment attached to the cell?
 
B

Bob Phillips

No, you need VBA

Public Sub Test()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
MsgBox cnt
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Bob Phillips

After pasting it in a code module, go back to Excel, select a range of
cells, then Alt-F8, select Test from the list, and hit run. You should get a
message telling you how many.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
S

Stonewall Rubberbow

Thanks, that does work. What I would ultimately like for it to do is place
the result in a cell, so I don't have to run it. Is there any solution for
that?
 
B

Bob Phillips

Make it as a UDF

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatilre
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Sub

then use =CommentCount() in a cell. Note that it won't automatically update
when comments are added, deleted as these will not trigger recalculation,
but will update on a recalculation, forced or natirally occurring.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top