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

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

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

On Tue, 9 Sep 2008 02:58:00 -0700, Richard <(E-Mail Removed)>
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

On Tue, 09 Sep 2008 07:24:15 -0400, Ron Rosenfeld <(E-Mail Removed)>
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