Excel VBA using NETWORKDAYS

Question

I have created a sub that checks if changes has happened in column F and writes the timestamp to a corresponding cell in column G. How do i edit this sub to return the network days in column H by finding the difference between the timestamp in column H and Cell containing a week commencing date A1? Without VBA, the formula is =ABS(NETWORKDAYS(A1, B1) - SIGN(NETWORKDAYS(A1, H1)). Below is my code so far. Any help?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
  Application.EnableEvents = False
  With Target
    'check if change happened in column F
    If .Column = 6 Then
      'check if changed value is X
      If Not IsEmpty(c) Then
        'add datestamp if it is
         Cells(.Row, 7).Value = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
         Cells(.Row, 8).Value = ABS(NETWORKDAYS(G5,H2)-SIGN(NETWORKDAYS(G5,H2)
      Else
        'clear datestamp and Column H if not
        Cells(.Row, 7).Value = ""
        Cells(.Row, 8).Value = ""
      End If
    End If
  End With
  Application.EnableEvents = True
End Sub

'

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
  Dim d1 As Date, d2 As Date, wf As WorksheetFunction
  Dim N As Long

  Set wf = Application.WorksheetFunction
  Application.EnableEvents = False
  With Target
    'check if change happened in column F
    If .Column = 2 Then
      'check if changed value is X
      If Not IsEmpty(c) Then
        'add datestamp if it is
        d1 = Cells.Range("A1")
        d2 = Cells.Range("B1:B2")
        N = wf.NetworkDays(d1, d2)
        Cells(.Row, 4).Value = N

      Else
        'clear datestamp and Colunm H if not
        Cells(.Row, 4).Value = ""
      End If
    End If
  End With
  Application.EnableEvents = True
End Sub

What are you getting? Your source code doesn't have matching closing parentheses in the NETWORKDAYS line. — ariscris
– ariscris, Commented Oct 20, 2014 at 13:28
The datestamp works as expected but the network day part does not — WKI
– WKI, Commented Oct 20, 2014 at 14:20
I edited indents except the extra End Sub that you should check. It is impossible that you see two Sub with thee End Sub on the screen. — hynekcer
– hynekcer, Commented Oct 20, 2014 at 18:41

Gary's Student · Accepted Answer · 2014-10-20 14:00:35Z

2

Here is how to use Networkdays() in VBA

Sub dural()
    Dim d1 As Date, d2 As Date, wf As WorksheetFunction
    Dim N As Long
    Set wf = Application.WorksheetFunction
    d1 = DateValue("1/1/2014")
    d2 = DateValue("12/31/2014")
    N = wf.NetworkDays(d1, d2)
    MsgBox N
End Sub

answered Oct 20, 2014 at 14:00

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

n8. · Accepted Answer · 2014-10-20 17:57:11Z

0

Once I cleaned up your indentations it's pretty clear that you have an "End Sub" in the middle of an "If" statement in your "Worksheet_Change" sub. Formatting helps you catch this kind of thing.

answered Oct 20, 2014 at 17:57

n8.

1,7443 gold badges16 silver badges42 bronze badges

Collectives™ on Stack Overflow

Excel VBA using NETWORKDAYS

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related