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