1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: How to add weekday count to my VB code

Discussion in 'Business Applications' started by DJ_Rutts, Dec 8, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Hi guys,

    Below is an extract of VB code (which I had help with) that I use on one of my work sheets, to calculate the amount of days a job has been outstanding. I want to change this so it only calculates week days but without messing up the rest of the code, can anyone help with this please?

    I have tried exchanging the WorksheetFunction.days360 to WorksheetFunction.weekdays but this doesn't seem to work.

    ' PSS duration
    If Cells(Rng.Row, "K").Value <> vbNullString And Cells(Rng.Row, "J").Value <> vbNullString Then
    Cells(Rng.Row, "L").Value = WorksheetFunction.days360(Cells(Rng.Row, "J").Value, Cells(Rng.Row, "K").Value)
    Else
    Cells(Rng.Row, "L").ClearContents
    End If


    Cheers

    Dan
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Have you tried the functon Networkdays() instead?

    =WorksheetFunction.Networkdays(Cells(Rng.Row, "J").Value, Cells(Rng.Row, "K").Value)
     
  3. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Hi again,

    I just gave it a go but it doesn't seem to want to work for me..?

    I will attach a copy of the spreadsheet for you.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    First of all if error trapping is on this won't work. When I turned error trapping off it returned an error.
    I'll have to check it.

    With error trapping on everyhting that goes wrong immediately skips the rest so nothing happens.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    It seems that Networkdays is not supported in VBA, the formula works fine but not in VBA, you'll have to write a function to do that
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I added a function Workingdays(d1,d2)

    I didn't put the unhide / hide code in
     

    Attached Files:

  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I did some editting, if there are empty cells in either B or C then the columns are unhidden as well as entering a new jow.

    The question will only be popped when there is one or more empty cells in B:C
     

    Attached Files:

  8. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Hi Keebellah,

    thanks for all your help with this.

    For some reason though I am still getting an error message for the 'workingdays'?

    Dan
     

    Attached Files:

  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    The function WorkingDays() does not exist, that's what it's telling you.
     
  10. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Hmmm.. is there a way to use GetWorkDays in this code? I am really struggling to find something that will work? I have tried a few ways but nothing seems to function.
     
  11. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Sorry - I realise what I had done wrong now! I had not copied it across correctly!!
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Okay if the UDFmodule from my sample is complete, the function goes with it, that's what was probably missing.
     
  13. DJ_Rutts

    DJ_Rutts Thread Starter

    Joined:
    Nov 9, 2010
    Messages:
    32
    Thanks for all your help with this one Hans. It's working. All good. :)
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Don&#8217;t forget to mark your own threads solved using the button at the top of the page of the thread in the upper left corner

    :)
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1030311

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice