excel - 使用 NetworkDays_Intl 的运行时错误 1004

Question

尝试运行以下代码时，我收到“运行时错误 1004”。我试过打破它，但就是看不到是什么触发了。任何想法将不胜感激！

ThisWorkbook.Sheets("Processing").Cells(i, 14) = (WorksheetFunction.NetworkDays_Intl(ThisWorkbook.Sheets("Processing").Cells(i - 1, 2), ThisWorkbook.Sheets("Processing").Cells(i, 2), 1, (ThisWorkbook.Sheets("Validation").Range(Cells(3, 3), Cells(31, 3)) - 1)) _
                    * (ThisWorkbook.Sheets("Validation").Cells(3, 2) - ThisWorkbook.Sheets("Validation").Cells(3, 1)) _
                    + Calc _
                    - WorksheetFunction.Median( _
                                                WorksheetFunction.NetworkDays_Intl(ThisWorkbook.Sheets("Processing").Cells(i - 1, 2), ThisWorkbook.Sheets("Processing").Cells(i - 1, 2), 1, ThisWorkbook.Sheets("Validation").Range(Cells(3, 3), Cells(31, 3))) * ThisWorkbook.Sheets("Processing").Cells(i - 1, 2) Mod 1, _
                         ThisWorkbook.Sheets("Validation").Cells(3, 1), _
                         ThisWorkbook.Sheets("Validation").Cells(3, 2)))

完整代码：

Sub PendingCustomer()

Dim i, LastRow As Integer
Dim Calc As Integer

    LastRow = ThisWorkbook.Sheets("Processing").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To LastRow
        If (ThisWorkbook.Sheets("Processing").Cells(i, 10) = "3") Or (ThisWorkbook.Sheets("Processing").Cells(i, 10) = "4") Then
            If (ThisWorkbook.Sheets("Processing").Cells(i, 5) = "Pending - Customer") And (UCase(ThisWorkbook.Sheets("Processing").Cells(i, 9)) Like "VZB*") And (ThisWorkbook.Sheets("Processing").Cells(i, 8) > ThisWorkbook.Sheets("Processing").Cells(i - 1, 8)) Then
                If WorksheetFunction.NetworkDays_Intl(ThisWorkbook.Sheets("Processing").Cells(i, 2), ThisWorkbook.Sheets("Processing").Cells(i, 2), 1, ThisWorkbook.Sheets("Validation").Range("C3:C31")) > 0 Then
                    Calc = WorksheetFunction.Median(ThisWorkbook.Sheets("Processing").Cells(i, 2) Mod 1, ThisWorkbook.Sheets("Validation").Cells(2, 2), ThisWorkbook.Sheets("Validation").Cells(3, 2))
                Else: Calc = ThisWorkbook.Sheets("Validation").Cells(3, 2)
                End If
                
                ThisWorkbook.Sheets("Processing").Cells(i, 14) = (WorksheetFunction.NetworkDays_Intl(ThisWorkbook.Sheets("Processing").Cells(i - 1, 2), ThisWorkbook.Sheets("Processing").Cells(i, 2), 1, (ThisWorkbook.Sheets("Validation").Range(Cells(3, 3), Cells(31, 3)) - 1)) _
                    * (ThisWorkbook.Sheets("Validation").Cells(3, 2) - ThisWorkbook.Sheets("Validation").Cells(3, 1)) _
                    + Calc _
                    - WorksheetFunction.Median( _
                                                WorksheetFunction.NetworkDays_Intl(ThisWorkbook.Sheets("Processing").Cells(i - 1, 2), ThisWorkbook.Sheets("Processing").Cells(i - 1, 2), 1, ThisWorkbook.Sheets("Validation").Range(Cells(3, 3), Cells(31, 3))) * ThisWorkbook.Sheets("Processing").Cells(i - 1, 2) Mod 1, _
                         ThisWorkbook.Sheets("Validation").Cells(3, 1), _
                         ThisWorkbook.Sheets("Validation").Cells(3, 2)))
                
            End If
        ElseIf (ThisWorkbook.Sheets("Processing").Cells(i, 5) = "Pending - Customer") And (UCase(ThisWorkbook.Sheets("Processing").Cells(i, 9)) Like "VZB*") And (ThisWorkbook.Sheets("Processing").Cells(i, 8) > ThisWorkbook.Sheets("Processing").Cells(i - 1, 8)) Then
            ThisWorkbook.Sheets("Processing").Cells(i, 14) = ThisWorkbook.Sheets("Processing").Cells(i, 2) - ThisWorkbook.Sheets("Processing").Cells(i - 1, 2)
        Else: ThisWorkbook.Sheets("Processing").Cells(i, 14) = ""
        End If
        
    Next
    
    ThisWorkbook.Sheets("Processing").Columns(14).NumberFormat = "[mm]:ss"

End Sub

数据集：

验证选项卡

处理选项卡

标签： excelvba

Answer 1

发现问题。

在 NetworkDays_Intl 函数中：

.Range(Cells(3, 3), Cells(31, 3)) 

不起作用，不得不使用

.Range("C3:C31")