Manpower Calendar Distribution, Skipping weekends.
From the following table, Calendar in Row 1 Starting in Col D,
and Data in Columns A, B, C Starting in Row 2. I need to distribute the Men, using the Start Date and duration, while skipping Fridays, saturdays and Sundays. Expected results shown starting in Column D. .. .. A B C D E F G H 1. Start Duration Men 7/21 7/22 7/23 7/24 7/25 2. 7/21/2011 2 5 5 5 .. Sub CalDist() Dim LastRow, LastCol As Long Dim Dur As Integer ' Duration in Days Dim Men, d As Integer Dim Start As String ' Starting Date LastRow = Range("A65536").End(xlUp).Row LastCol = Range("IV1").End(xlToLeft).Column + 3 'Populating the Table For r = 2 To LastRow Dur = Cells(r, 2) Men = Cells(r, 3) For c = 4 To LastCol Start = Cells(r, 1) If Start = Cells(1, c) Then 'Distribute Men for the Duration For d = 0 To Dur - 1 '--------------------------------------------------------------- ' If Start Weekday = Friday, Saturday or Sunday, skip the Column ' Problem with the following If's. ' If the Start Date is already a Friday, Saturday or Saturday ' the If's are processed fine. ' If the Start Date is any other WeekDay and the Duration ' would go over the next Week, the If's are ignored. ' I cannot find my Error. '--------------------------------------------------------------- If Weekday(Cells(1, c)) = 6 Then c = c + 1 If Weekday(Cells(1, c)) = 7 Then c = c + 1 If Weekday(Cells(1, c)) = 1 Then c = c + 1 Cells(r, c + d) = Men Next d End If Next c Next r End Sub |
All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com