"Invalid procedure call or argument" error in Do-While-Loop in Excel VBA?

Question

Why is there an

"Invalid procedure call or argument"

error in this Do-While-Loop in Excel VBA?

I cannot seem to find the problem I do not believe there is a typo. The error is directed at the line of code with the asterisks.

Note that Worksheets("DTR").Cells(i,3) is a date.

For i = 2 To Total_rows_DTR
    m = Application.Match(Worksheets("DTR").Cells(i, 3), Worksheets("Holidays Table").Range("A2:A1048576"), 0)
    If Not IsError(m) Then
        If Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3), Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "Regular" Then
            x = 1
            '** Error occurs in the following line:
            Do While Application.WorksheetFunction.Weekday(Worksheets("DTR").Cells(i, 3) - x) = 1 Or Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3)-x, Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "Regular" Or Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3)-x, Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "SNWH" And Application.WorksheetFunction.Sum(Worksheets("DTR").Range("P" & i-x & ":S" & i-x)) > 0
                x = x + 1
            Loop
            If Application.WorksheetFunction.Sum(Worksheets("DTR").Range("P" & i & ":S" & i)) = 0 Then
                Worksheets("DTR").Cells(i, 26) = 0
            End If
        End If
    End If
Next i

Answer 1

1. First solution (see 2 for updated conditions)

Having a look at your loop …

Do While Application.WorksheetFunction.Weekday(Worksheets("DTR").Cells(i, 3) - x) = 1 _
   Or Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3), Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "Regular" _
   Or Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3), Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "SNWH" _
   And Application.WorksheetFunction.Sum(Worksheets("DTR").Range("P" & i & ":S" & i)) > 0
    x = x + 1
Loop

where you only increase x, the only x depending part of your loop is the right before first Or.

Application.WorksheetFunction.Weekday(Worksheets("DTR").Cells(i, 3) - x) = 1
'only part that depends on x

So all the other statements can be checked before the loop because they are static (relative to x).

So your loop is calculating less in each iteration than before because A, B and C are only calculated once. We can do this because they don't change by iterating the loop.

A = Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3), Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "Regular"
B = Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3), Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "SNWH"
C = Application.WorksheetFunction.Sum(Worksheets("DTR").Range("P" & i & ":S" & i)) > 0

Do While Application.WorksheetFunction.Weekday(Worksheets("DTR").Cells(i, 3) - x) = 1 _
   Or A _
   Or B _
   And C
    x = x + 1
Loop

---

2. Update since OP changed the loop code to …

Do While Application.WorksheetFunction.Weekday(Worksheets("DTR").Cells(i, 3) - x) = 1 _
    Or Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3)-x, Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "Regular" _
    Or Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3)-x, Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "SNWH" _
    And Application.WorksheetFunction.Sum(Worksheets("DTR").Range("P" & i-x & ":S" & i-x)) > 0
    x = x + 1
Loop

… where every of the 4 conditions depends on x, we can do something like this:

Dim RunLoop As Boolean
Do
    'we split up the conditions into A-D so if an error occurs we know in which condition
    A = Application.WorksheetFunction.Weekday(Worksheets("DTR").Cells(i, 3) - x) = 1
    B = Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3) - x, Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "Regular"
    C = Application.WorksheetFunction.Index(Worksheets("Holidays Table").Range("B2:B1048576"), Application.WorksheetFunction.Match(Worksheets("DTR").Cells(i, 3) - x, Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "SNWH"
    D = Application.WorksheetFunction.Sum(Worksheets("DTR").Range("P" & i - x & ":S" & i - x)) > 0
    'now we check all coditions
    RunLoop = A Or B Or C And D
    'and exit the loop if the condition is false
    If RunLoop = False Then Exit Do
    x = x + 1
Loop

Note: I additionally recommend to use Option Explicit and declare every variable properly.

Answer 2

Writing extreme big conditions in the loop are not considered clean code. As mentioned, split the condition to separate boolean functions and evaluate them separately. Like this:

Public Sub TestMe()

    While isIt1 Or isItRegular
        'do something
    Wend

End Sub

Public Function isIt1() As Boolean

    isIt1 = Application.WorksheetFunction.Weekday(Worksheets("DTR").Cells(i, 3) - x) = 1

End Function

Public Function isItRegular() As Boolean

    With Application.WorksheetFunction
        isItRegular = .index(Worksheets("Holidays Table").Range("B2:B1048576"), .match(Worksheets("DTR").Cells(i, 3), Worksheets("Holidays Table").Range("A2:A1048576"), 0)) = "Regular"
    End With

End Function

Answer 3

Your Do...Loop is too complicated. More info here or here.