VBA to Calculate Start Date from End Date and Dev Time Using WORKDAY()

Hi Forum and thanks in advance!

I'm working on a planning tool and would like to provide the user a "recommended latest start date" for a project tactic using the WORKDAY function. I need them to be able to reference it after entering their development time and desired target completion date in another column for the tactic in that row. I'd like to show this latest start date in a shape (I've named it LatestStartDateProject) on the worksheet and have it change when they move to different active rows.

Here's my failed attempt so far:

Please Login or Register  to view this content.

I have this code sitting in the ProjectTacticalPlan worksheet module. The WorkDay function approach I've used above basically attempts to reverse the logic of the function to count backwards (it works well as a formula in the cell, but in short I cannot use that in this case as it's not compatible with the user experience). I've reviewed many threads but can't get no action on the formulas I've tried!

Any help is much appreciated! Please let me know if you have questions.

Thanks,
Chris

Thanks for the quick response XLAdept! Sorry I'm just now getting back to this, can't believe it's been this long already!

Good news is that I was able to take your code (and education) and get it to work with my selection change routine I had set up with other functionality on this sheet. I just added a little error handling (which I'm sure could be done more elegantly) based on how it was functioning. Really appreciate your help as this gets me closer to "done" with this crazy thing.

I wasn't sure what you meant by my not using the variable "Holidays"...that is a named range on another sheet ("HolidayList") I'm using to exclude holidays in my calculation of work days between the StartDate and EndDate. Maybe the validity of your question is why the code you've provided didn't pick up that named range in the calculation of StartDate. I had to actually list the range specifically as B1:B28 to get it to work and see those holidays. If you have any suggestions there, I'd appreciate your thoughts on how to make the named range work instead.

Please Login or Register  to view this content.

Otherwise, thanks again!

In gratitude,
Chris