OzGrid

How to count the number of cells containing a comment that contains the word LATE

Requirement:

The user is working on a college's attendance registers and, up to now, have had no problems with the formulae to calculate attendance figures (absent, authorised absence, present, etc.).

The user has now been asked to include a column to indicate the number of times a student has been late. This means that the cell will have the date of the class, but include a comment that simply says 'LATE'. The user needs a column that just counts the number of time a 'LATE' comment has been included.

This would be easy if only the late cells had a comment, but the authorised absence cells also include a comment with the reason they have been given the absence. The user has worked out how to 'CountComments' (thanks to this forum) but the user only needs the cumulative figure for LATE.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1205021-ability-to-count-the-number-of-cells-containing-a-comment-that-contains-the-word-late

Solution:

Try this macro. It should place the number of "lates" in column AL.

Option Compare Text
Sub CountLates()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long, count As Long: count = 0
    LastRow = Range("G" & Rows.count).End(xlUp).Row
    Dim rng As Range
    For x = 3 To LastRow
        For Each rng In Range("G" & x & ":AD" & x)
            If Not rng.Comment Is Nothing Then
                If InStr(rng.Comment.Text, "Late") > 0 Then
                    count = count + 1
                End If
            End If
        Next rng
        Range("AL" & x) = count
        count = 0
    Next x
    Application.ScreenUpdating = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery