Excel VBA script for loop with dates

Question

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.

Sample data:

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

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

Answer 1

You are not assigning any values to variables st or en.

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

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.

If ((Vlookup(d,lookups!$o$3:$p$26,2,false))=1) or (weekend(d))Then

Rewrite as:

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