How to code For...Each statement

[BobLoblaws]

More specifically, does anyone know how I would go about coding this. Here is the pseudo code.

For Each 'Wednesday' IN 2002
' Do Something
Next

Thanks,

 

[ReneGoos]

Do you mean something like this?

Dim datFirst As Date, datLast As Date
Dim intFirstDay As Integer, intMyDay As Integer
intMyDay = vbWednesday
datFirst = CDate("1-1-2002&quot
'keep it less then the first of the next year
datLast = CDate("1-1-2003&quot
'difference between the first day of the year and my day
intFirstDay = (intMyDay - Weekday(datFirst)) Mod 7
'set datFirst to the first wednesday of the year
datFirst = CDate("1-1-2002&quot + intFirstDay

'now loop
Do While datFirst < datLast
'here you do something
MsgBox datFirst
datFirst = datFirst + 7
Loop

 

[Kavius]

Try this, its untested and I'm having a coffeeless morning so consider yourself warned.

dim i as integer
dim d as integer

'start the date at the first of the year
i = '1-Jan-2002'
'find out what day of the week it currently is.
'In this case I have started the week at Wednesday
d = Weekday(i, vbWednesday)

'move the date forward to wednesday(I am totally unsure
'of this math and its way to early in the morning to
'actually figure it out, and the coffe machines broken 
'here so...I hope you get the idea.
i = i + 7 - d
'do stuff while it's 2002
while (year(i) = 2002)
  'Do Something
  'increment date by 1 week
  i = i + 7
wend

 

[Snaggs]

ReneGoos,

Why is the Mod 7 in the function? Also if you had a weekday greater than vbWednesday wouldn't that throw you back to the previous year? Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.

 

[Kavius]

Sorry, one off error. Wednesday should be the end of our week. (I told you I was having coffee issues this morning).

d = weekday('1-Jan-2002', vbThursday)

'd' will now contain a value of 6 since the first day of this year was a Tuesday, and I have started the week on Thursday.

i = i + 7 - d

So 7 - d = 1, and '1-Jan-2002' + 1 = '2-Jan-2002' which is the first Wednesday of the year.

 

[ReneGoos]

About the Mod 7. I've checked your question and saw what I feel is a bug in VB. The Mod 7 should map to a value between 0 and 6 always, ..,-7,0,7,14,21,.. Mod 7 = 0; ..,-6,1,8,15,22,.. Mod 7 = 1; ...; ..-1,6,13,20,27,.. Mod 7 = 6. However VB calculates -1 Mod 7 =-1!! This is clearly going against the definition of Mod.
However using kavius' solution working with Weekday(..,vbThursday) you get (no need for Mod):
Dim datFirst As Date, datLast As Date
Dim intFirstDay As Integer
datFirst = CDate(&quot;1-1-2002&quot
'keep it less then the first of the next year
datLast = CDate(&quot;1-1-2003&quot
'difference between the first day of the year and my day
intFirstDay = 7 - Weekday(datFirst, vbThursday)
'set datFirst to the first wednesday of the year
datFirst = CDate(&quot;1-1-2002&quot + intFirstDay

'now loop
Do While datFirst < datLast
'here you do something
MsgBox datFirst
datFirst = datFirst + 7
Loop

 

[Snaggs]

ReneGoos,

Thanks for clearing that up. The MOD part of the equation didn't make sence to me because WeekDay only returns a number 1-7 no matter what day of the week it is. Had the function used the Day() function, it would have made more sense to me. Snaggs
tribesaddict@swbell.net
Life can only be understood backwards; but it must be lived forwards.