Date Entry
I have 2 drop down lists. 1 so that I can select the day ie Sunday the other
so that I can select a period ie Year 2009. Would it be possible to have a macro create a list of all the dates which match the criteria ie all Sundays in 2009. Thanks in advance Richard |
Date Entry
Try this code. I hard coded the year and day of week. Change as required.
the code puts the dates in column A Sub MakeDates() MyYear = 2009 'Change to Range("B1") or equivalent DayofWeek = "Tuesday" 'Change to Range("C1") or equivalent. DayCount = 0 Do DayCount = DayCount + 1 DayName = WeekdayName(DayCount, False, vbSunday) Loop While DayofWeek < DayName FirstDay = DateValue("1/1/" & MyYear) LastDay = DateValue("12/31/" & MyYear) 'Get First Day of year that matches DayofWeek DayOffset = DayCount - Weekday(FirstDay) If DayOffset < 0 Then DayOffset = DayOffset + 7 FirstDay = FirstDay + DayOffset RowCount = 1 For DayCount = FirstDay To LastDay Step 7 Range("A" & RowCount) = Format(DayCount, "MMMM DD, YYYY") RowCount = RowCount + 1 Next DayCount End Sub "Richard" wrote: I have 2 drop down lists. 1 so that I can select the day ie Sunday the other so that I can select a period ie Year 2009. Would it be possible to have a macro create a list of all the dates which match the criteria ie all Sundays in 2009. Thanks in advance Richard |
Date Entry
Thanks I'll give it a go
"Joel" wrote: Try this code. I hard coded the year and day of week. Change as required. the code puts the dates in column A Sub MakeDates() MyYear = 2009 'Change to Range("B1") or equivalent DayofWeek = "Tuesday" 'Change to Range("C1") or equivalent. DayCount = 0 Do DayCount = DayCount + 1 DayName = WeekdayName(DayCount, False, vbSunday) Loop While DayofWeek < DayName FirstDay = DateValue("1/1/" & MyYear) LastDay = DateValue("12/31/" & MyYear) 'Get First Day of year that matches DayofWeek DayOffset = DayCount - Weekday(FirstDay) If DayOffset < 0 Then DayOffset = DayOffset + 7 FirstDay = FirstDay + DayOffset RowCount = 1 For DayCount = FirstDay To LastDay Step 7 Range("A" & RowCount) = Format(DayCount, "MMMM DD, YYYY") RowCount = RowCount + 1 Next DayCount End Sub "Richard" wrote: I have 2 drop down lists. 1 so that I can select the day ie Sunday the other so that I can select a period ie Year 2009. Would it be possible to have a macro create a list of all the dates which match the criteria ie all Sundays in 2009. Thanks in advance Richard |
Date Entry
On Tue, 9 Sep 2008 02:58:00 -0700, Richard
wrote: I have 2 drop down lists. 1 so that I can select the day ie Sunday the other so that I can select a period ie Year 2009. Would it be possible to have a macro create a list of all the dates which match the criteria ie all Sundays in 2009. Thanks in advance Richard This was written as a worksheet change macro, so the change in the list would occur whenever you changed the inputs. To enter this, right click on the sheet tab; select View Code; and paste the code below into the window that opens: =================================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rInputs As Range Dim rOutput As Range Dim lWeekday As Long Dim lYear As Long Dim lFirstDay As Long Dim i As Long Set rInputs = Range("A1:A2") Set rOutput = Range("B1:B53") 'Change made in A1 or A2? If Not Intersect(Target, rInputs) Is Nothing And _ WorksheetFunction.CountA(rInputs) = 2 Then rOutput.Clear rOutput.NumberFormat = "dddd, mmmm dd, yyyy" With Range("A1") 'Assumes weekday list in order Sunday, Monday ... Saturday lWeekday = WorksheetFunction.Match(.Value, Range(Mid(.Validation.Formula1, 2)), 0) End With lYear = Range("A2").Value lFirstDay = DateSerial(lYear, 1, 8) - _ Weekday(DateSerial(lYear, 1, 8 - lWeekday)) [b2].Value = lFirstDay End If i = 0 Do Until Year(lFirstDay + 7 * i) < lYear rOutput(i + 1, 1) = lFirstDay + 7 * i i = i + 1 Loop End Sub =========================== --ron |
Date Entry
On Tue, 09 Sep 2008 07:24:15 -0400, Ron Rosenfeld
wrote: lWeekday = WorksheetFunction.Match(.Value, Range(Mid(.Validation.Formula1, 2)), 0) The above line may be word-wrapped inappropriately on your reader (it is on mine). It should be all one line. --ron |
All times are GMT +1. The time now is 01:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com