Business Day through VBA on a range of cells

Hi Experts,

Quick and small help required. I googled quite a lot for this but was unable to find any.

I want to input the Yesterday's Business Date on a range of cells say for E.g from column L2-L59.

Two criteria's are important.

1) Should be yesterday's business date. I.e. If today is 08/06/2014 then my the code should update as 08/05/2014. If i am running the code on a Monday, then it should update the date as friday.

2) The range can vary. Today it might be from L2-L59, tomorrow it can be L2-L70.


Can you please help.

hi aaron.mendes, option

Hi watersev,

Thanks. I inserted your code in my macro but somehow this is not working. Have i missed something or inputted a wrong function?

My code is at the end just before the save as csv code begins. Please can you help.

Please Login or Register  to view this content.

hi aaron, the code provided should reside in Sheet level and will not work in a standard module as it is an event code. If you do not want an event code (triggered by changing value in L column) the code should be rewritten

Hi Watersev - I'm an amateur in VBA and I am still learning to write codes. The above code that I pasted, is just a result of quite a lot of copy paste, trial and error and ofcourse google

If the code has to be rewritten, would you please help.

please check attachment, press Run button or run code "test1"(ALT+F8, select "test1", Run)