VBA Sub to Find the Date of the First Working Monday in the Month

[Tegglet]

I need a VBA sub to find the day number for the first working Monday in the month and to enter the value into cells C5 to C49 as appropriate.

Cells C5 to C49 will be populated with the day number appropriate to the item described in cells F5 to F49 either by VBA or by direct entry, as required. The values act as a seed to calculate the values for cells A5 to A49 and B5 to B49. The reason for doing it this way is outside the scope of this problem and way too complex to describe here. Please just accept it as it is.

Cell B4 on the active sheet contains the date for the first of the month. For example 01-05-2021 (using dd-mm-yyyy format) is entered as 01-05-2021 rather than being derived from a formula.

The list of the public holidays for the current year is held in cells A1:A11 on a separate sheet named “Holidays”.

If the first Monday in the month is a public holiday then I need the day number for the previous Friday, or, in the case of Easter Monday, that for the previous Thursday.

The workday.Intl seems to fit the bill but I am getting a plethora of meaningless and confusing error messages and nothing that even remotely works.

The first problem is trying to establish the difference is between Workday_Intl and Workday.Intl, is it a "_" or a dot. The results from Web searches seem to use them interchangeably. What is the difference? Is there one?

Sub routines or code fragments that purport to work copied from such pages just give errors.

I need a fresh pair of eyes on the problem as I am currently going round in circles.

Can anybody help?

Many thanks
Tegglet

[Tegglet]

I think I have finally beaten this problem into submission!!

It would appear that Workday.Intl is entered directly into a cell in the spreadsheet and Workday_Intl is used in VBA code. This I discovered by accident! Confirmation and expansion would be much appreciated.

It would also appear that I need to prepend Workday_Int with WorksheetFunction (WorksheetFunction.Workday_Int ), again confirmation and expansion would be much appreciated.

These two obstacles removed I think I have managed to suss out the other obscure error messages.

However, any suggestions or help would still be appreciated.

[bsalv]

look at column L of "Holidays"

[Tegglet]

Hi Bsalv,
Thanks for responding.

I am not sure what I am looking at!

What is the significance of the curly brackets?

[bsalv]

You asked a VBA solution to find your day.
When you look in the VBA-editor, you find an UDF (user defined function) "FirstMonday" in module1.
If you give this UDF a day, it gives you the first monday of that same month and in case of a holiday, it goes to the previous week.

You can also use in in your worksheet, see columns G en L.
In G you can add a date, with the formula in L you get the wanted date.
The other columns can be deleted.

[Tegglet]

Hi Bsalv,
Sorry about the delay in responding, I got sidetracked by another problem.

It would appear that Workday.Intl is entered directly into a cell in the spreadsheet and Workday_Intl is used in VBA code. This I discovered by accident! Confirmation and expansion would be much appreciated.

It would also appear that I need to prepend Workday_Int with WorksheetFunction (WorksheetFunction.Workday_Int ), again confirmation and expansion would be much appreciated.

Can I take it from the way you have used the two versions of Workday... that my conclusions are correct?

One problem I am experiencing is checking months with no Bank Holiday. Can Workday... be used to return the original date if it does not find a date in the "Holidays" list?

I should have made clear in my original post that I need a sub not a function. The overall project is to use VBA to create a spreadsheet from scratch that will form the basis for monitoring and forecasting cash flow during the year in question.

Your code will take a lot of deciphering for me as most of it is way outside my comfort zone and converting it to a sub is going to be a non trivial task!

Many thanks for your response
Tegglet

[bsalv]

the macro "New_12Months_Schedule" uses the UDF to create the 12 first mondays for the next year.