0

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
Improve this question

3 Answers 3

Reset to default
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.

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

7 Comments

Yes that is true. I actually corrected it just a moment ago. I made them dynamic relative to x. Edited the main post sorry for that
And while I was watching through the whole condition of the loop I was wondering whether there would be someone else to do the same. You have even gone 2 steps further, by analyzing it.
@MarcSantos I improved the answer due to your edit of the conditions. Note that if we split up the conditions that way you can see in which condition an error occurs. This way it is much easier to debug.
@Peh Thank you for your help. I will get back to this after I fix the logic of the code. I realized I need 2 Do-While-Loop for my objective and i'm still trying to figure out how to do it.
The main reason I figured out why the loop is failing is because of the Application.WorksheetFunction.Match portion. When I turn it dynamic (e.g. Application.WorksheetFunction.Match(Cells(i,3)-x)) it will cause the program to error when it cannot find a match in the designated table. So I fixed it accordingly. Just to update you
|
0

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

Improve this answer

2 Comments

I read it however it does not state about the limits of the loop which my code supposedly broke due to complexity
@MarcSantos - are you serious?
0

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
Improve this answer

1 Comment

Thanks for this I actually do not know the language well since it's only been about a month since I started and there's a lot of syntax that I do not know of i'll try to break down my code. Organizing the code has been a pain given the time constraint I have

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.