I have written a simple User Defined Function (UDF) called CONSEQOFF() that you can install into our workbooks as needed and it will provide the new count function you need.
Option Explicit
Function CONSEQOFF(Workdays As Range) As Long
Dim Cnt As Long, cell As Range
For Each cell In Workdays
Select Case cell.Value
Case Is = ""
CONSEQOFF = WorksheetFunction.Max(CONSEQOFF, Cnt)
Cnt = 0
Case Is = "MIA", "MIAOUT", "UPTO"
Cnt = Cnt + 1
Case Is = "HOLOFF", "DAYOFF"
'skip, do nothing
End Select
Next cell
CONSEQOFF = WorksheetFunction.Max(CONSEQOFF, Cnt)
End Function
After it installed, it is used in a cell like any other function.
=CONSEQOFF(Workdays)
Workdays = the range of cells you want to evaluate
=========
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook
The function is installed and ready to use.
Bookmarks