Excel VBA脚本与日期循环 [英] Excel VBA script for loop with dates

问题描述

我正在计算两个给定日期之间的工作时间（上午8点至晚上8点），不包括周末和公众假期，但是我的代码语法不正确。

I am calculating the number of work hours (8am to 8pm) between the 2 given dates, excluding Weekends and Public holidays, but my code syntax is incorrect.

示例数据：

开始日期：2011年6月17日上午08:00:00

Start day: 17/06/2011 08:00:00 AM

结束日期：2011年6月19日下午

End day: 19/06/2011 08:00:00 PM

Sub SLA_Days_Resolved_F()
    Dim x As Integer
    ' Set numrows = number of rows of data.
    NumRows = Range("F2", Range("F2").End(xlDown)).Rows.Count

    Dim total As Integer 'to count the total hours
    Dim st As String 'start date cell
    Dim en As String 'end date cell
    Dim destCell As String
    Dim d As Date ' for the loop

    total = 0

    ' Establish "For" loop to loop "numrows" number of times.
    For x = 2 To NumRows + 1
        st = "G" & CStr(x) 'reference to the cells
        en = "D" & CStr(x)
        'loop from start date to end date
        For d = Date(Range(st)) To Date(Range(en))
            'check if the current date is found is a Public holiday in the range or if a weekend
            If ((Vlookup(d,lookups!$o$3:$p$26,2,false))=1) or (weekend(d))Then
                'minus 8 to remove hours before 8am.
                total = (total + Hour(d) + minutes(d) / 60) - 8
            End If
        Next
    Next
End Sub

推荐答案

您没有为变量 st 或 en 。

Date 在VBA中不可用。您可能需要使用DateSerial函数。这是一个循环日期的简单示例，您应该可以修改该日期。

Date is not a function available in VBA. You will probably need to use DateSerial function. Here is a simple example of looping over dates which you should be able to modify.

Sub LoopDates()
Dim d As Date
'Loop the days beteween today and March 1, 2013.
For d = DateSerial(Year(Now), Month(Now), Day(Now)) To DateSerial(2013, 3, 1)

    Debug.Print d  'Prints the "d" value in the immediate window.
Next

End Sub

此外，您不能只需将工作表公式放在VBA中即可。对于Vlookup，此行绝对是错误的语法，并且 Weekend 并不是我所知道的公式（测试似乎可以确认它不是对工作表或工作表的有效调用）

Also, you can't just put worksheet formulae in VBA. This line is definitely wrong syntax for Vlookup, and Weekend is not a formula that I'm aware of (testing it seems to confirm it is not a valid call on worksheet or in VBA.

如果（（（Vlookup（d，lookups！$ o $ 3：$ p $ 26,2，false））= 1）或（周末（d））然后

重写为：

If Application.WorksheetFunction.Vlookup(d,Sheets("lookups").Range("$o$3:$p$26"),2,false)=1 _
    or Not Application.WorksheetFunction.Weekday(d) Then

另一个例子以我认为更有效的方式确定了变量的大小：

ANOTHER EXAMPLE of a date loop where I have dimensioned the variables in what I believe to be a more efficient manner:

Sub Test()

Dim st As Range
Dim x As Integer
Dim stDate As Date
Dim enDate As Date
Dim d As Date
Dim numRows as Long

NumRows = Range("F2", Range("F2").End(xlDown)).Rows.Count

For x = 0 To NumRows-2
'SET YOUR VARIABLES HERE
' This may seem redundant or unnecessary for this case, but it makes structuring nested
' loops easier to work with, and then there are fewer places to make changes, 
' if you need to make changes.

    Set st = Range("G2").Offset(x, 0)
    Set en = Range("D2").Offset(x, 0)
    stDate = DateSerial(Year(st), Month(st), Day(st))
    enDate = DateSerial(Year(en), Month(en), Day(en))

    'Then, loop through the dates as necessary
    For d = stDate To enDate
        Debug.Print d
        'Do your code here.
    Next

Next


End Sub

这篇关于Excel VBA脚本与日期循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！