Announcement

Collapse
No announcement yet.

Unable to get the NetworkDays property

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Unable to get the NetworkDays property



    Hi Guys,

    I have created following code and each time I receive a run time error- Unable to get the NetworkDays property.

    Sub macro_ordery3()

    Dim wsOD As Worksheet
    Dim sFolderPath As String
    Dim SAPwb As Workbook
    Dim lastrow As Long
    Dim i As Long

    Application.DisplayAlerts = False

    Set wsOD = Worksheets("ORDERDATA")


    ' opening ZSE file

    With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Please select ZSE file"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 0 Then GoTo EndLine
    sFolderPath = .SelectedItems(1)
    End With

    Set SAPwb = Workbooks.Open(sFolderPath, UpdateLinks:=False)

    'copying data


    SAPwb.Worksheets(1).UsedRange.Copy
    wsOD.Range("A1").PasteSpecial xlPasteValues

    wsOD.Range("D3").Value = "Performance"

    ' counting networkdays

    lastrow = wsOD.Range("A" & wsOD.Rows.Count).End(xlUp).Row

    For i = 5 To lastrow
    wsOD.Cells(i, 4).Formula = Application.WorksheetFunction.NetworkDays(wsOD.Cells(i, 3), wsOD.Cells(i, 2)) - 1
    Next i

    EndLine:

    End Sub


    Please see attached macro file and data.
    Attached Files

  • #2
    This error happens when parameters are not of correct type - here they must be dates (in your file it only looks like dates)

    change your code as below (only cast your dates with CDATE()

    Code:
    wsOD.Cells(i, 4).Value = Application.WorksheetFunction.NetworkDays(CDate(wsOD.Cells(i, 3)), CDate(wsOD.Cells(i, 2))) - 1
    :-)

    Comment


    • #3


      Originally posted by guew19 View Post
      This error happens when parameters are not of correct type - here they must be dates (in your file it only looks like dates)

      change your code as below (only cast your dates with CDATE()

      Code:
      wsOD.Cells(i, 4).Value = Application.WorksheetFunction.NetworkDays(CDate(wsOD.Cells(i, 3)), CDate(wsOD.Cells(i, 2))) - 1
      :-)
      Work perfectly guew19, thank you!

      Comment

      Working...
      X