Break Rota with VBA.

Post the pretty workbook.

Originally Posted by hyding

Sorry to be a pain!

You are not being a pain, but if you don't provide a sample workbook, then in essence, you are asking somebody else to mock-up a workbook and then supply the answer.

The folks on this forum are excellent at providing answers, but not many want to mock-up your sample.

Note: take a quick read of the "yellow" banner.

I'll try and look tomorrow. Someone else might have picked it up by then ...

Originally Posted by TMS

I'll try and look tomorrow. Someone else might have picked it up by then ...

Great, thanks a lot mate

One thing to note, on the blank workbook I provided in a previous comment, there will be another colum next to the first colum on the left (where the staff member's names will go).

In this additional colum is where the shifts will be inputted.

Wondered about that. It would probably make life easier if the shift start and end values were in separate columns? Is that possible? Not necessarily a show stopper but it will make the formula complex ( I expect ).

So the names are in column A, shifts in column B, and everything else starting in column C??

Ok. And, for the sake of argument, start times, end times, and time slots ( column headers) will all be time values in the format "hh:mm".

Goodnight

Here's a worked example based on your sample file.

Please look at the Test Area at the bottom for the examples.

There's no code. Just a formula in cell H50 copied down and across. Cells F50:G59 have Data Validation to select shift start or end at 15 minute intervals. Row 60 has totals (for the test area).

In principle, this doesn't require VBA. It's possible to drop the formula into new rows using a Worksheet Change Event handler but, personally, I think that would be overkill.

You could achieve much the same outcome by using a Structured Table. You'd have to get rid of the merged cells ... but I’d do that anyway and just double the height of each row.

I'd also move the reference data elsewhere, for example, onto the second sheet that I added. Being realistic, I wouldn't have thought you'd want to print this and having it way over on the right isn't that convenient for referring to it.

If you want a VBA solution, please clarify your requirements/expectations based on the updated example.

Just wondered if you found the solution proposed useful?

Either way, you may want to provide some feedback and, if it was, mark the thread solved.

Incidentally, please be aware that files that you upload often have a user name, or two, included. From that/those, anyone who is interested can Google your name and gather information about you and where you are employed (currently and in the past), where you live, where you went to school, and so on. You may want to edit the Author and any other details, particularly on company related files.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.