Hi Rolf. Thanks for coming back to me. I have completed this process manually now, but would still like to achieve an automated method.
Basically, I have a list of 47 different shift patterns for my 120 staff. I am looking at when the most staff are at work, and when I am quietest. I have designed a userform with 3 comboboxes. 1 for day of week. 1 for start time, and 1 for end time. I am looking for when I unload the userform, a "1" is added to the corresponding cells. So if I select, Monday 08:00 start and 12:00 finish, and click ok, I would like the userform to enter a "1" into each 15 minute segment between 08:00 and 12:00. As I go through each staff members shifts, going through this process, I am expecting more than just one staff member to be in between 08:00 and 12:00, so the userform unloading would add the "1" for that staff member to the total already in the cell, whether it's 10 or 30 - depending upon how many times I have unloaded the userform with comboboxes covering that timespan.
The manual process I have done, was simply to have all of the staff names and shifts along row a (b1 and right) and 08:00 (monday) to 18:00 (friday) down column a in 15 minute chunks, and just entered the "1"'s for each staff members shifts, but this was laborious. And then at the very right hand side, just an =sum(a3:ac3) for example, and dragged it all of the way down.
Does this make sense? Thanks, paul