Networkdays Function for range of cells in VBA

Question

I am building a macro which will compare dates (in date format dd/mm/yyyy hh:mm) from column AO with dates (in date format dd/mm/yyyy hh:mm) in column AL and it will print their difference in working days to the column AS (just the number of the days, not minutes etc.) I have searched and found that NETWORKDAYS is a function in excel that does that but I dont know how to implement it for a range in VBA. My code so far, is pretty similar to one I found in the web but it is for specific dates, not for a range. Any ideas? Much appreciated!

What I have so far is this, but it says there is an error in line with d1=cell.Offset... I dont now why!

Dim d1 As Range, d2 As Range, wf As WorksheetFunction
'Dim N As Long
Set wf = Application.WorksheetFunction
For Each cell In Range(Range("AT2"), Range("AT2").End(xlDown))
Set d1 = cell.Offset(0, -4)
Set d2 = cell.Offset(0, -7)
cell.Value = wf.NetworkDays(d1.Value2, d2.Value2)
Next cell

Answer 1

I suggest a 'hybrid' approach as you are going to use a worksheet function anyway: let VBA fill in the function, replace the output with values:

    Sub WorkDaysDiff()
    ' w-b 2017-08-26

    Dim rng As Range, lastrow As Long

    ' assuming columns A, B hold dates, results into column C
    lastrow = ActiveSheet.Range("A1").End(xlDown).Row
    With ActiveSheet
        Set rng = .Range(.Range("C1"), .Range("C" & lastrow))
    End With

    With rng
        ' write formula into worksheet cells for whole range at once
        ' and replace it with their value after recalculation
        .FormulaR1C1 = "=NETWORKDAYS(RC[-1],RC[-2])"
        .Copy
        .PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With
End Sub

This way, you avoid looping which might save time if the range is big enough.

Answer 2

You don't need VBA for this. It may also be a VBA function, I'm not sure.

Answer 3

try this

    Dim d1 As Date, d2 As Date

    For Each Cell In Range(Range("AT2"), Range("AT2").End(xlDown))
        d1 = Cell.Offset(0, -4)
        d2 = Cell.Offset(0, -7)
        Cell.Value = Application.WorksheetFunction.NetworkDays(d1, d2)
    Next Cell

Answer 4

If you want to use column AL and AO, and put the results in AS - with the range determined by what is in AT - use the following:

Dim d1 As Range, d2 As Range, wf As WorksheetFunction
'Dim N As Long
Set wf = Application.WorksheetFunction
For Each cell In Range(Range("AT2"), Range("AT2").End(xlDown))
Set d1 = cell.Offset(0, -5)
Set d2 = cell.Offset(0, -8)
cell.Offset(0, -1).Value = wf.NetworkDays(d1.Value2, d2.Value2)
Next cell