VBA宏从两个日期列中减去工作日

Question

目前，我正在尝试使用 Networkdays.Intl 从两列中减去两天之间的工作日。

但错误显示

Object-required.

我需要如何解决这个问题？我实际上不确定我使用 Networkdays.Intl 的方式是否正确。

这是我的代码:

Private Sub MinusWeekDays()
    Dim InitialRow As Long
    Dim EndRow As Long
    Dim lrow As Long

    Dim CurrentSheet As Worksheet
    Set CurrentSheet = Excel.ActiveSheet

    InitialRow = CurrentSheet.UsedRange.Cells(1).Row
    EndRow = CurrentSheet.UsedRange.Rows(CurrentSheet.UsedRange.Rows.count).Row

    For lrow = EndRow To InitialRow Step -1
        CurrentSheet.Cells(lrow, "AH").Value = NetworkDays.Intl((CurrentSheet.Cells(lrow, "W").Value), (CurrentSheet.Cells(lrow, "V").Value), (CurrentSheet.Cells(lrow, "AG").Value))
    Next lrow
End Sub

我还尝试通过执行 Dim intdays As Integer, intdays = 11 coz 将 CurrentSheet.Cells(lrow, "AG").Value) 直接替换为 '11' 以避免需要创建仅值为 11 但似乎失败的列.

Answer 1

您似乎错过了 WorksheetFunction object从调用 NETWORKDAYS.INTL function .我已将对工作表的重复调用移至周围的 With ... End With statement .

WITH CurrentSheet
    For lrow = EndRow To InitialRow Step -1
        .Cells(lrow, "AH").Value = WORKSHEETFUNCTION.NetworkDays_Intl(.Cells(lrow, "W"), .Cells(lrow, "V"), 11)
    Next lrow
END WITH

每行的周末类型真的那么不同吗？不能只在第三个参数中输入一个数字而不是 .Cells(lrow, "AG") ?查看 NETWORKDAYS.INTL function 的官方文档以确保您提供正确的参数。