how to get count of comment inside cell

Hi,

I have a question, how to get the count of comment inside a cell (insert comment).

I have attached a file which has comment. please guide me.

Theonly comments that I can see are Changed - 7 letters.

yes, but i just need the count if a cell has a comment, comment can be any thing.

in my attached sheet, in the cell AL2, i need the answer, for example, in the second row of my attached sheet, only 7 cell contain comments. so my answer should be 7.

Function CountComments(ByVal rng As Range)
Dim tmp As Range
    For Each tmp In rng
        CountComments = CountComments - (Not tmp.Comment Is Nothing)
    Next tmp
End Function

i have pasted the code in macros, what should i do next to get the count.

hey it worked i got the answer,

i have one more question.

Eg. i have three columns (A, B, C)

if A or B contains Comment it should return "Critical" and if C contains Comment it should return "Non Critical".

Please guide me..

Thanks a lot..

Function CommentStatus(ByVal rng As Range)
    If (Not rng.Cells(1, 1).Comment Is Nothing) Or _
        (Not rng.Cells(1, 2).Comment Is Nothing) Then
       
        CommentStatus = "Critical"
    ElseIf (Not rng.Cells(1, 1).Comment Is Nothing) Then
       
        CommentStatus = "Non Critical"
    End If
End Function

hi bob, it worked perfectly, thanks a lot,

i have a doubt, i used the formula =CountComments(A11:A11) -- it worked

but when i used =CountComments(A11:A11, A23) it dint work.

please guide me.

No, it you have multiple ranges you have to explicitly cater for it.

This should work fine for any number of ranges

Function CountComments(ParamArray cells() As Variant)
Dim rng As Variant
Dim tmp As Range
    For Each rng In cells
        For Each tmp In rng
            CountComments = CountComments - (Not tmp.Comment Is Nothing)
        Next tmp
    Next rng
End Function

hey bob its super cool, it worked fantastically. thanks a lot.

I am wondering, if i want to implement this coding to more than one excel sheets, do i need to copy the script in all other sheets. i have more than 50 excel sheets where i need to count the cell has comments. how do i bring the same function in all 50 sheets. is there any easy way to do that. ?

Are you referring to 50 sheets in one workboo, or separate workboks?

separte workbooks.

Then you need to create an addin and put the function in that addin.

I always have a utility addin in my systesm for exactly this sort of thing.

Can you explain me how to do addin, please.

what is that record macro and personal macro workbook. if you know please explain me.