Posts by pbarrettlkh

Quote from Mumps

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

Display More

Thank you SOOOOOOOO much - that works brilliantly! I now plan to grow back all the hair I had pulled out!

Thanks Mumps – I’ve attached a copy of the register system I’ve been working on, but removed all of the names of the individuals – leaving the current ‘marking scheme’ in place along with the formulae and results.

So, basically, the formulae are in place to calculate attendance, possible attendance, number of sessions, etc. as can be seen from the columns AE-AK; this is based on the fact that dates are used to confirm attendance, ‘x’ for unauthorised absences and ‘AA’ for authorised absences. When an individual as an ‘AA’, a comment is inserted to inform us of their reasons.

When an individual is late, the date of the class (to confirm attendance) is inserted, but so is a comment to indicate lateness (at the moment the comment itself varies, but the idea is regulate this – eg. ‘LATE 10 mins’ or LATE 25 mins’.

The academic staff and principals have suggested that a column be added to show the number of times a student has been late, but maintain the current way of recording attendance.

I would like to have a column that simply shows the number of times a comment with the word ‘LATE’ has been inserted on each row. As there are other comments inserted for those who have been absent, I can’t just count the number of comments.

I have tried to create a formula without success, and my knowledge of macros is incredibly limited.

This sounds easy but I'm finding the task incredibly difficult.

I am working on our college's attendance registers and, up to now, have had no problems with the formulae to calculate attendance figures (absent, authorised absence, present, etc.). I have 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'. I need 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. I've worked out how to 'CountComments' (thanks to this forum) but I only need the cumulative figure for LATE.

I'm pulling my hair out! Can anyone help?