Nsryjdtyk

I need help on this. I'm doing a report and inserting comments on cells. How do I count cells with in a range in excel with specific value that has a comment using vba or formula?

Here is one way. It loops through each cell you pass in the range and checks if there is a comment. If so, it adds it to a counter. This is probably going to be pretty expensive if used on large range, but it should at least get you started:

Add to a regular module:

Function CommentCounter(rng As Range) As Integer

    Dim cell As Range

    Dim counter As Integer

    Dim currentComment As String





    For Each cell In rng

        On Error Resume Next

        currentComment = cell.Comment.Text

        If Len(currentComment) > 0 Then counter = counter + 1

        currentComment = ""

    Next cell



    CommentCounter = counter

End Function

Just saw the part about having a specific value AND a comment. This should get you going:

Function CommentCounter(rng As Range) As Integer

    Dim cell As Range

    Dim counter As Integer

    Dim currentComment As String

    Dim specificValue As String



    specificValue = "Something Specific"





    For Each cell In rng

        On Error Resume Next

        currentComment = cell.Comment.Text

        If cell.Value = specificValue And Len(currentComment) > 0 Then counter = counter + 1

        currentComment = ""

    Next cell



    CommentCounter = counter

End Function

=COUNTIF(A:A;"comment")

Where A:A specifies that you want to examine the whole column of A. Instead of A:A, you could also use A1:A3, which means examine A1, A2 and A3.

EDIT:

If you want to count the cells with comments (not with the word "comment"), I would suggest to do the following:

=COUNT(A1:A3) - COUNTBLANK(A1:A3)