Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF to count cells that have an attached comment
Can I use COUNTIF (or anything) to count the number of cells in a range that
have a comment attached to the cell? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF to count cells that have an attached comment
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF to count cells that have an attached comment
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF to count cells that have an attached comment
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF to count cells that have an attached comment
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF to count cells that have an attached comment
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells with numbers and ignore cells with errors | Excel Discussion (Misc queries) | |||
How to count blank cells that contain formula? | Excel Discussion (Misc queries) | |||
How to count interstitial blank cells? | Excel Discussion (Misc queries) | |||
Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions | |||
Count number of shaded cells | Excel Discussion (Misc queries) |