CODEWITHSUNDEEP
vbaexceldate
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Networkdays Function for range of cells in VBA

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

Upvotes: 1

Views: 2012

Answers (4)

user1016274
user1016274

Reputation: 4209

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.

Upvotes: 1

YowE3K
YowE3K

Reputation: 23994

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

Upvotes: 0

jsotola
jsotola

Reputation: 2278

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

Upvotes: 0

gattoun
gattoun

Reputation: 106

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

Upvotes: 0

Related Questions