Filling Column with date
You didn't mention if you always start on the first of the month. The earlier
version I posted assumed that was true and will add extra days on the end if
you do not start on the first. This version corrects that issue.
Roy
Private Sub CommandButton1_Click()
Dim StartMonth, Dayz, DayCode, x As Integer
Dim Letter As String
If IsDate(Range("A5")) = True Then
Range("B5:CO6").Select
Range("B5:CO6").ClearContents
Range("A5").Activate
StartMonth = Month(Range("A5"))
Select Case StartMonth
Case Is = 1, 12
Dayz = 90
Case Is = 2
Dayz = 89
Case Is = 3, 5, 6, 7, 8, 10, 11
Dayz = 92
Case Is = 4, 9
Dayz = 91
End Select
Dayz = Dayz - Day(Range("A5")) + 1 'just in case you don't start a
month on the 1st
Select Case Month(Range("A5"))
Case Is = 12
x = (Year(Range("A5")) + 1) Mod 4
Case Is = 1, 2
x = Year(Range("A5")) Mod 4
End Select
If x = 0 Then Dayz = Dayz + 1
End If
Application.ScreenUpdating = False
For x = 1 To Dayz
Cells(5, x + 1).Value = Day(Range("A5") + x - 1)
Cells(5, x + 1).NumberFormat = "#"
DayCode = Weekday(Range("A5") + x - 1)
Select Case DayCode
Case Is = 1, 7
Letter = "S"
Case Is = 2
Letter = "M"
Case Is = 3, 5
Letter = "T"
Case Is = 4
Letter = "W"
Case Is = 6
Letter = "F"
End Select
Cells(6, x + 1).Value = Letter
Next
Application.ScreenUpdating = True
End Sub
"Bill" wrote:
I need some help. I have a spredsheet that displays three months of dates.
I place the start date in cell A5 like 1 Dec 04. Then starting in cloumn B5,
the Date is place in the cell. In cell B6, the day is insert.
EXAMPLE
A5 = 1 Dec 04 then
B5 = 1, and B6 = W for Wednesday
C5 = 2, and C6 = T for Thursday
D5 = 3, and D6 = F for Friday
this sequence would need to continue until the through the third month. As
in the case of this example it would need to continue through 28 Feb 05. Can
anyone please provide me the VBA code to accomplishs this tasks.
Thanks
Bill
|