Date Entry

R

Richard

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
 
J

Joel

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
 
R

Richard

Thanks I'll give it a go

Joel said:
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
Click to expand...
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

Ron Rosenfeld

lWeekday = WorksheetFunction.Match(.Value,
Range(Mid(.Validation.Formula1, 2)), 0)
Click to expand...

The above line may be word-wrapped inappropriately on your reader (it is on
mine). It should be all one line.
--ron
 
Top