[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?

[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)

"Stonewall Rubberbow" wrote
in message ...
Can I use COUNTIF (or anything) to count the number of cells in a range
that
have a comment attached to the cell?

[Stonewall Rubberbow]

Bob, thanks for the quick reply. I'm familiar with Excel, but not how to add
VBA, or what to do with the formula once it has been added. I tried to copy
your post, and paste it into the sheet's code window in Visual Basic, but I
don't know what to do next, or how this affects the worksheet. Can you walk
me through this a little bit?

"Bob Phillips" wrote:

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)

"Stonewall Rubberbow" wrote
in message ...
Can I use COUNTIF (or anything) to count the number of cells in a range
that
have a comment attached to the cell?

[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)

"Stonewall Rubberbow" wrote
in message ...
Bob, thanks for the quick reply. I'm familiar with Excel, but not how to
add
VBA, or what to do with the formula once it has been added. I tried to
copy
your post, and paste it into the sheet's code window in Visual Basic, but
I
don't know what to do next, or how this affects the worksheet. Can you
walk
me through this a little bit?

"Bob Phillips" wrote:

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)

"Stonewall Rubberbow"
wrote
in message ...
Can I use COUNTIF (or anything) to count the number of cells in a
range
that
have a comment attached to the cell?

[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?

"Bob Phillips" wrote:

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)

"Stonewall Rubberbow" wrote
in message ...
Bob, thanks for the quick reply. I'm familiar with Excel, but not how to
add
VBA, or what to do with the formula once it has been added. I tried to
copy
your post, and paste it into the sheet's code window in Visual Basic, but
I
don't know what to do next, or how this affects the worksheet. Can you
walk
me through this a little bit?

"Bob Phillips" wrote:

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)

"Stonewall Rubberbow"
wrote
in message ...
Can I use COUNTIF (or anything) to count the number of cells in a
range
that
have a comment attached to the cell?

[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)

"Stonewall Rubberbow" wrote
in message ...
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?

"Bob Phillips" wrote:

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)

"Stonewall Rubberbow"
wrote
in message ...
Bob, thanks for the quick reply. I'm familiar with Excel, but not how
to
add
VBA, or what to do with the formula once it has been added. I tried to
copy
your post, and paste it into the sheet's code window in Visual Basic,
but
I
don't know what to do next, or how this affects the worksheet. Can you
walk
me through this a little bit?

"Bob Phillips" wrote:

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)

"Stonewall Rubberbow"
wrote
in message
...
Can I use COUNTIF (or anything) to count the number of cells in a
range
that
have a comment attached to the cell?