UDF for Attendance register

Solved/Closed
slinger42 Posts 2 Registration date Sunday October 30, 2022 Status Member Last seen October 31, 2022 - Oct 30, 2022 at 12:37 PM
 Slinger42 - Nov 3, 2022 at 09:48 PM

Hello,

I have used your UDF as shown below to report a date whenever an entry is made in a row of my excel sheet.

Function LastAttendanceDate(mDates, mTimes As Range)
Dim mCol As Integer
For Each cell In mTimes
    If cell.Value <> vbNullString Then
        mCol = cell.Column
    End If
Next cell
LastAttendanceDate = Cells(mDates.Row, mCol)
End Function.

What I need to to do in addition to this is to IGNORE the characters O (Thats a letter O), or a W and not to change the date if they are used. In other words the date of the last attendance must not change if either of these characters are entered in a row.

I have tried many things but I am now so confused I need your help.

Thanks in anticipation.


Macintosh / Safari 16.0

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 3, 2022 at 12:39 PM

Hi Slinger,

For that you will need to add the extra conditions to code line 4:
 

Function LastAttendanceDate(mDates, mTimes As Range)
Dim mCol As Integer
For Each cell In mTimes
    If cell.Value <> vbNullString And cell.Value <> "O" And cell.Value <> "W" Then
        mCol = cell.Column
    End If
Next cell
LastAttendanceDate = Cells(mDates.Row, mCol)
End Function

Best regards,
Trowa


HI TrowaD

Thanks for the solution worked well.

Did not even think of extending the IF statement like that.

Many thanks. Saved me more hours of misguided frustration !!

Regards