How to count comments?

[HatGuy]

I keep attendance and grades in a spreadsheet. The rows are students and
the columns are class dates and assignments.

I add comments to individual date or assignment cells to keep track of
special situations (reasons for absences, notes on conversations, ...
whatever).

I would like to add a column that counts the number of comments in a
row. Can I do that somehow with =if(... or =countif(... somehow?

If not, I've seen a vba macro for the purpose. Could I make the macro
execute automatically when I enter a new comment?

Thanks!

 

[Harlan Grove]

...
...

If not, I've seen a vba macro for the purpose. Could I make the macro
execute automatically when I enter a new comment?

You will need VBA, and you could write a user-defined function. For example, the
following udf is fed a range (it assumes a single area range, and enforces that
assumption by restricting itself to the first area of the range) and returns a
matching array of TRUE/FALSE values indicating whether corresponding cells in
the range have/don't have comments. You could use the function's result in
counting formulas.


Function HasComment(rng As Range) As Variant
Dim i As Long, j As Long, m As Long, n As Long, rv As Variant

Set rng = rng.Areas(1)
m = rng.Rows.Count
n = rng.Columns.Count
ReDim rv(1 To m, 1 To n) As Boolean

For i = 1 To m

For j = 1 To n

If Not rng.Cells(i, j).Comment Is Nothing Then rv(i, j) = True

Next j

Next i

If m = 1 And n = 1 Then HasComment = rv(1, 1) Else HasComment = rv

End Function