How to use Workday function using vba?

[laxminarayana]

Hi,

I'm trying to insert the follwing line in a cell.
"Please send the file on MM/DD/YYYY"

I have written the following code, in which the date will be pulled from cell "B1".

Sub autotext()
Worksheets("Sheet1").select
Range("D1").Value = "Please send the file on" & Space (1) & Format(Worksheets("Sheet1").Range("B1")) &
End Sub

But i want the date to be a working day. I have a list of holidays on my other worksheet. So how do i tell excel to put a working day, using vba?

Thank you.

[Ron Coderre]

Try something like this:

Please Login or Register  to view this content.

Note: Adjust the HolidayRange reference to suit your situation.



Is that something you can work with?

[laxminarayana]

Try something like this:

Please Login or Register  to view this content.

Note: Adjust the HolidayRange reference to suit your situaion.



Is that something you can work with?

Thank You Ron, that helped me a lot.

[laxminarayana]

Hi Ron,

If the case is like 4 days after the date present in cell "B8". But only the fourth day should be working day, i.e if the 4th count falls on a non working day then moving to next working day.

I've tried with the following code but did not get the desired result.

Please Login or Register  to view this content.

Can you please help me with this.

Thank You.

[Ron Coderre]

Try this:

Please Login or Register  to view this content.

[laxminarayana]

Hi,

The above code is not working. I've attached my excel file. Can you please have a look at the file.

Thank You.

[Ron Coderre]

The VBA code needs to be in a general module...not a sheet module
• ALT+F11...to open the VBA editor
• Select your workbook from the project window
• Insert.Module...to create a General Module
• Cut the code from the "Actual" code module and paste it into the new module

Last: Right-click on your button and assign the autotext macro to it.

Does that help?

[laxminarayana]

I've moved the code to Module, but still not working.

[Ron Coderre]

OK...Define "not working".
When I follow the instructions I posted and click the button, the correct date is entered in the correct cell.

[laxminarayana]

When i enter the date as 3/1/14 and fourth day after the date is 3/5/14. since 3/5/14 is in holiday list, the result should be 3/6/14. But actual result is showing as 3/10/14.

[Ron Coderre]

Got it....The WORKDAY function only counts actual workdays. However, it seems that you want to count actual days, including weekends, but not including holidays? Is that the rule?

EDITED:
I deleted my previous misguided effort because I think I figured out what you want.
This code begins with the reference date, adds 3, then finds the next workday after that.

Please Login or Register  to view this content.

Is that something you can work with?

[laxminarayana]

Thank You very much