Solved: Problems with creating a shift-plan for a new year

[EirikDaude]

I'm having some trouble with automating the creation of a shift-plan for a new year. Basically I just need to fill in the dates in the plan, with the starting position depending on what shift works the first date of the year. I've gone through the code several times, but can't find out where I've made an error. As a last resort, I'm posting the worksheet here, so some of you hopefully can help.

[Bob Phillips]

I think this needs a lot more explanation Eirik.

I assume SkiftVeke means Shift Week, but what are the numbers in the cells, the dates? Why red numbers? What is the lower table? What is being created? and so on, and so on.

[EirikDaude]

Yes, skiftveke means shift week. The numbers in the cells are the dates, and the numbers in red are holidays - I'll probably just change them all to black if I get it to update automatically. The data in the lower table is just an overview over these same holidays, so that will probably also be removed.

What I want the macro to do is just filling in the dates for the various months, starting in the shift-week specified in the select-statement at the top of the macro, and looping back to shift-week 1 when it reaches the end of shift-week 5.

I hope this answered most of your questions - feel free to ask if there's anything else that seems unclear.

[Bob Phillips]

Hi Eirik,

That is helpful, but I still need more I feel.

What I am lacking is an understanding of how once the user answer the questions regarding year and shift number, what is the logic to fill in those dates. I just cannot see why January starts at 14 for shift 1, goes through to 31, has a gap, and the re-starts at 1 in the middle of shift 4. That is just an example, it applies to all months. ANd how do you know what are the holidays? And what does that table at the bottom signify?

[EirikDaude]

Ah, I see why you're confused now. What "skiftveke", or shift-week if you will, it is, is not what determines what shift is at work, it's just a way to divide up the 5-week cycle the plan works over. The way to read the plan is to draw a line from a given date down to the five rows named "SKIFT 1", "SKIFT 2" etc. and see what is written there (F means your working the morning shift, E the evening shift, and N the night shift, with 12 hour morning and night shifts in the weekend). So if shift 3 works the morning shift in the first week of 2009 (what's used in the current lay-out), the macro starts counting the days of January from shift-week 4.

You should also note that I considered the entire range A1:AJ21 to be one table, so the table naming the holidays is the one in A23:L30. What's contained in A16:AJ21 is the work-schedule for the five shifts following the plan (as explained in the paragraph above).

As for the holidays, they're just there because I used an old shift-plan without any macros as a basis for the one I'm trying to make. I think I mentioned that I'll remove this from the final worksheet since they'll be very hard to determine automatically.

The problem I'm having is that instead of jumping nicely from e.g. 30. April (a Wednesday) to 1. May (supposed to be a Thursday), it counts an extra day in April and then jumps to the Monday of the next week before starting to count the days of May - and it doesn't even do it consistently, as at least the first three months fill in correctly. It also seems to skip the 1. of some months, for no reason I can find.

I hope this helped you understand the plan a bit better. And again, feel free to ask if there's anything else you feel I should clear up. Also ask if there's anything in this explanation that was hard to understand - I feel a few of the sentences were a bit clumsy :)

[Bob Phillips]

Eirik,

I am afraid I still have absolutely no idea how and why this works, there is probably a key phrase that is implicit to you that I am missing. Butm I can see what is happening if I run the macro, so I am going to work my way through it and see if I can fix it. I will get back to you.

[Bob Phillips]

Eirik,

As well as having extra days in the 30 day months, and losing day on occasions, you were also losing days in the months (e.g there is no 8 May in your example).

I feel the problem is down to your loops withing loops, the control is not maintained, so I have re-written it to remove those inner loops, and make it much simpler.

I hope I haven't messed it up.



Option Explicit
Option Base 1

Sub newYear()
Dim thisDate As Long, startdate As Date, thisMonth As Long, thisYear As Long
Dim thisWeekday As Integer, startShift As Long, shiftWeek As Long
Dim dateCol As Long
Dim daysInMonth As Variant
Dim startDate As Date

With Sheets("Skiftplan")

With .Range("B3")

On Error Resume Next
thisYear = CInt(InputBox("Kva år vil du lage ny skiftplan for?", "Oppgje årstal"))
If thisYear = 0 Then Exit Sub
startDate = DateSerial(thisYear, 1, 1)
startShift = CInt(InputBox("Kva skift jobber føremiddag " & startDate - WeekDay(startdate, 2) + 1 & "?", "Skift"))
If startShift = 0 Then Exit Sub
On Error GoTo 0
.Resize(12, 35).ClearContents
.Offset(-2, -1).Value = thisYear

daysInMonth = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
If Month(DateSerial(thisYear, 2, 29)) = 2 Then daysInMonth(2) = 29
thisWeekday = WeekDay(startDate, 2)

Select Case startShift

Case 1: shiftWeek = 3
Case 2: shiftWeek = 1
Case 3: shiftWeek = 4
Case 4: shiftWeek = 2
Case 5: shiftWeek = 5
End Select

dateCol = (shiftWeek - 1) * 7 + (thisWeekday - 1)
For thisMonth = 1 To 12

For thisDate = 1 To daysInMonth(thisMonth)

If dateCol >= 35 Then dateCol = 0
.Offset(thisMonth - 1, dateCol).Value = thisDate
dateCol = dateCol + 1
Next thisDate
Next thisMonth
End With
End With
End Sub

[EirikDaude]

As far as I can tell this did exactly what I wanted it to - thanks a lot for the help :D

I did find a slight mess-up, though it was very easy to fix:


Dim thisDate As Long, startdate As Date, thisMonth As Long, thisYear As Long
Dim thisWeekday As Integer, startShift As Long, shiftWeek As Long
Dim dateCol As Long
Dim daysInMonth As Variant
Dim startDate As Date

You can't declare startDate twice ;)

Thanks a lot for the help. I hope it didn't take too much of your time. Your solution was a lot more elegant than what I was trying to acchieve.

Just out of curiosity, is writing loops within loops generally problematic in VBA? I can't see any reason the program should lose count, though obviously it did...

[Bob Phillips]

As far as I can tell this did exactly what I wanted it to - thanks a lot for the help :D

I did find a slight mess-up, though it was very easy to fix:

You can't declare startDate twice ;)

No idea how that crept in as I tested it :)


Thanks a lot for the help. I hope it didn't take too much of your time. Your solution was a lot more elegant than what I was trying to acchieve.

Not at all, it was nice to get something I had to think about, I enjoyed it. Understanding what you were doing took a bit of time, but once I dived into the code ...


Just out of curiosity, is writing loops within loops generally problematic in VBA? I can't see any reason the program should lose count, though obviously it did...

It is not a problem per se, but it can become a problem if you are incrementing the counter for the outer loop within the inner loop. In these circumstances you have to be very careful and ensure you control it within the inner loop. In your case I could easily see how you were erroneously ratcheting the counter up to 31 for months with only 30 days, and I could see the day 1 disapperaing, couldn't quite figure how the other days disappeared, but by then I had decided that your code was too complex and I needed to simplify it, so I didn't look to hard for that reason.

[EirikDaude]

Yeah, I suspected it was my fault the macro lost count, but I just couldn't find the error in the code. I guess I should plan future projects a bit better from the start. It was really inspiring seeing how you simplified the code so much that I could hardly recognize it :) Hopefully I'll reach that level of expertise some day too :)

Yet again, thanks a lot for taking the time to help a beginner like me.

[Bob Phillips]

I would suggest that the most imporatnt thing that I did was to introduce an array od days in the month, that way I could remove the three Case staements for the different days in month statements, they were all basically doing the same thing, just for a different bound. Once I did that the code was already a lot simpler, and I could focus in on what it was doing.