Office VBA - Failure in logic criteria for a Do Until Loop

Question

So, I am writing a VBA macro that will count the number of instances where by an employee is absent through sickness. An instance of sickness is defined as a period of contiguous workdays where the employee is recorded as being sick (so this obviously excludes weekends). You can view my source data here

For some reason at run-time the logic is failing on this line: Do Until Cells(r, c).Value <> "Sick" And (Cells(4, c).Value <> "Sat" Or Cells(4, c).Value <> Sun") and is therefore counting each Monday-sickness as a new instance. For example, Sheet "Rota" Row 10 has two instances of sick, but the macro will report four instances.

Any help would be much appreciated.

Option Explicit

Sub AbsenceInstances()

    Dim dtToday, dtStart As Date
    Dim r, c, dblTodayCol, dblStartCol, dblInstances, dblAgentRow As Double
    Dim rngFindRow As Range

    Sheets("Rota").Select

'    dtToday = Int(Now())
    dtToday = "31/12/2019"
'    dtStart = dtToday - 364
    dtStart = "31/12/2018"

    'define upper & lower boundaries of measurement area
    On Error GoTo NotFound
        dblTodayCol = WorksheetFunction.Match(CLng(CDate(dtToday)), Sheets("Rota").Range("5:5"), 0)
        dblStartCol = WorksheetFunction.Match(CLng(CDate(dtStart)), Sheets("Rota").Range("5:5"), 0)
    On Error GoTo 0
    GoTo ContinueSub

NotFound:
    MsgBox "Please check that your data incorporates information back to " & dtStart & ", otherwise this function will not work.", vbCritical
    Exit Sub

ContinueSub:

    'loop through employee list to determine number of absences
    For r = 6 To 34
        For c = dblStartCol To dblTodayCol
            If Cells(r, c).Value = "Sick" Then
                ' an instance of sick is defined as contiguous days absence excluding weekends
                Do Until Cells(r, c).Value <> "Sick" And (Cells(4, c).Value <> "Sat" Or Cells(4, c).Value <> "Sun")
                    If Cells(4, c).Value = "Sat" Then c = c + 1
                    c = c + 1
                Loop
                dblInstances = dblInstances + 1
            End If
        Next c

    ' identify employee's data-row to output result
    Set rngFindRow = Sheets("Bradford scale").Range("A:A").Find(What:=Sheets("Rota").Range("B" & r).Value, _
                         After:=Range("A1"), LookIn:=xlValues, _
                         LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)

    If Not rngFindRow Is Nothing Then
        dblAgentRow = rngFindRow.Row
        Set rngFindRow = Nothing
    End If

    ' output result for employee
    Sheets("Bradford Scale").Cells(dblAgentRow, 5).Value = dblInstances

    ' reset for next employee
    dblInstances = 0

    Next r

    Sheets("Bradford Scale").Select

End Sub

CLR · Accepted Answer · 2019-10-02 11:26:13Z

1

Change your criteria to:

Do Until Cells(r, c).Value <> "Sick" And Cells(4, c).Value <> "Sat" And Cells(4, c).Value <> "Sun"

You can't use Or combined with Not (in this case <>) that way. The value can never be Not(Sat) OR Not(Sun) at the same time. But it can be Not(Sat) AND Not(Sun) - which is what you're after.

answered Oct 2, 2019 at 11:26

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Digitaldamo Over a year ago

Thank you CLR, that nailed it.

Collectives™ on Stack Overflow

Office VBA - Failure in logic criteria for a Do Until Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related