Solved: How to add weekday count to my VB code

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Have you tried the functon Networkdays() instead?

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

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

Attachments

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
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
The function WorkingDays() does not exist, that's what it's telling you.
 

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.
 

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!!
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Okay if the UDFmodule from my sample is complete, the function goes with it, that's what was probably missing.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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

:)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top