# OzGrid

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

Category: [Excel]  Demo Available

### 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.

Code:
```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:

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

stars (0 Reviews)