Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Column with date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Column with date
Bill
try something like: Application.ScreenUpdating = False ' Range("A5") = "01-Dec-04" Range("B5").FormulaR1C1 = "=RC[-1]" Range("C5").FormulaR1C1 = "=RC[-1]+1" Range("C5").AutoFill _ Destination:=Range("C5:CQ5") Range("B6").FormulaR1C1 = "=R[-1]C[-1]" Range("C6").FormulaR1C1 = "=RC[-1]+1" Range("C6").AutoFill _ Destination:=Range("C6:CQ6") Range("B5:CQ5").NumberFormat = "d" Range("B6:CQ6").NumberFormat = "ddd" Range("B5:CQ6").HorizontalAlignment = xlCenter Application.ScreenUpdating = True Bear in mind that the number of columns will depend on how many days there are in each of the months. I don't think there is a format that just gets the first letter of the day of the week, hence, Mon, Tue, Wed and so on. Regards Trevor "Bill" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Column with date
Bill,
Add a command button on your sheet and paste this code into the click event. It checks for a valid date format, clears the old B5:CO6 area, accounts for leap year and notes the days in a single letter configuration. Give it a shot. Roy Dim StartMonth, Dayz, x As Integer 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 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 = "#" Cells(6, x + 1).Value = Weekday(Range("A5") + x - 1) Select Case Cells(6, x + 1).Value Case Is = 1, 7 Cells(6, x + 1).Value = "S" Case Is = 2 Cells(6, x + 1).Value = "M" Case Is = 3, 5 Cells(6, x + 1).Value = "T" Case Is = 4 Cells(6, x + 1).Value = "W" Case Is = 6 Cells(6, x + 1).Value = "F" End Select Cells(6, x + 1).Value = Left(Cells(6, x + 1).Value, 1) Next Application.ScreenUpdating = True "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Column with date
This works great with one exception if I put in a month where days equal 92
it gives me 93 days across the sheet. Can you help resolve this. Thanks Bill "Roy" wrote: Bill, Add a command button on your sheet and paste this code into the click event. It checks for a valid date format, clears the old B5:CO6 area, accounts for leap year and notes the days in a single letter configuration. Give it a shot. Roy Dim StartMonth, Dayz, x As Integer 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 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 = "#" Cells(6, x + 1).Value = Weekday(Range("A5") + x - 1) Select Case Cells(6, x + 1).Value Case Is = 1, 7 Cells(6, x + 1).Value = "S" Case Is = 2 Cells(6, x + 1).Value = "M" Case Is = 3, 5 Cells(6, x + 1).Value = "T" Case Is = 4 Cells(6, x + 1).Value = "W" Case Is = 6 Cells(6, x + 1).Value = "F" End Select Cells(6, x + 1).Value = Left(Cells(6, x + 1).Value, 1) Next Application.ScreenUpdating = True "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Column with date
Great observation. It made it fairly easy to diagnose. The only way to get 93
days is with a leap year malfunction. Note that the variable x is unaffected by the Cases in the Select Case(Month(Range("A5")) section in all of the 92 day months. But since x was never initialized, it inherited zero by default. If we initialize it to anything else, we avoid the problem and the Case statements will change x as needed. See the one line addition below. That should do the trick. It's 1:40 am, so I'll let you test it and let me know. Roy "Bill" wrote: This works great with one exception if I put in a month where days equal 92 it gives me 93 days across the sheet. Can you help resolve this. Thanks Bill "Roy" wrote: Bill, Add a command button on your sheet and paste this code into the click event. It checks for a valid date format, clears the old B5:CO6 area, accounts for leap year and notes the days in a single letter configuration. Give it a shot. Roy Dim StartMonth, Dayz, x As Integer 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 x = 5 'we don't care what it is as long as it's not zero! OOPS! 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 = "#" Cells(6, x + 1).Value = Weekday(Range("A5") + x - 1) Select Case Cells(6, x + 1).Value Case Is = 1, 7 Cells(6, x + 1).Value = "S" Case Is = 2 Cells(6, x + 1).Value = "M" Case Is = 3, 5 Cells(6, x + 1).Value = "T" Case Is = 4 Cells(6, x + 1).Value = "W" Case Is = 6 Cells(6, x + 1).Value = "F" End Select Cells(6, x + 1).Value = Left(Cells(6, x + 1).Value, 1) Next Application.ScreenUpdating = True "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filling a column | New Users to Excel | |||
how to change column references, while filling down another column | Excel Discussion (Misc queries) | |||
Pre-filling column | Excel Discussion (Misc queries) | |||
filling a two column listbox from a two column recordset | Excel Programming | |||
Filling Column with Numbers | Excel Programming |