I have a spreadsheet that list when authorizations expire. Before the
expiration date, the authorization must be renewed in a meeting that occurs
every Wednesday. Thus far, the assigning of the renewal meetings has been
done manually. Is the a macro or lookup function that does this
automatically?

Example: if expiration is on 10/19/09, then assign to Wednesday date before
or on 10/19/09. Cell to show 10/21/09.

Thanks in advance!

Hi Visakha

You example and description contradicts.. However both options are given
below. Test and feedback

Example: if expiration is on 10/19/09, then assign to Wednesday date before
or on 10/19/09. Cell to show 10/21/09.


Sub Macro2()
Dim myDate As Date
myDate = DateValue("10/14/2009")

MsgBox "Wednesday before or current: " & DateAdd("d", _
IIf(Weekday(myDate) < 4, -3, 4) - Weekday(myDate), myDate)

MsgBox "Wednesday after or current: " & DateAdd("d", _
IIf(Weekday(myDate) <= 4, 4, 11) - Weekday(myDate), myDate)

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Visakha" wrote:

I have a spreadsheet that list when authorizations expire. Before the
expiration date, the authorization must be renewed in a meeting that occurs
every Wednesday. Thus far, the assigning of the renewal meetings has been
done manually. Is the a macro or lookup function that does this
automatically?

Example: if expiration is on 10/19/09, then assign to Wednesday date before
or on 10/19/09. Cell to show 10/21/09.

Thanks in advance!

Hi Jacob,

Thanks for catching that--I meant before or on. And yes, this macro works
but I am wondering how to apply it a column of cells. Say the expiration
dates are listed in column b (reiterating to up to 1000). Is there a way to
show the assigned date in column C instead of a dialog box? I am fairly new
to macros but am enjoying them! Thanks so much!

"Jacob Skaria" wrote:

Hi Visakha

You example and description contradicts.. However both options are given
below. Test and feedback

Example: if expiration is on 10/19/09, then assign to Wednesday date before
or on 10/19/09. Cell to show 10/21/09.


Sub Macro2()
Dim myDate As Date
myDate = DateValue("10/14/2009")

MsgBox "Wednesday before or current: " & DateAdd("d", _
IIf(Weekday(myDate) < 4, -3, 4) - Weekday(myDate), myDate)

MsgBox "Wednesday after or current: " & DateAdd("d", _
IIf(Weekday(myDate) <= 4, 4, 11) - Weekday(myDate), myDate)

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Visakha" wrote:

I have a spreadsheet that list when authorizations expire. Before the
expiration date, the authorization must be renewed in a meeting that occurs
every Wednesday. Thus far, the assigning of the renewal meetings has been
done manually. Is the a macro or lookup function that does this
automatically?

Example: if expiration is on 10/19/09, then assign to Wednesday date before
or on 10/19/09. Cell to show 10/21/09.

Thanks in advance!

You dont need a VBA solution to do this. With dates in Column B starting from
cell B1; in C1 enter formula

=B1+IF(WEEKDAY(B1)<4,-3,4)-WEEKDAY(B1)
and copy down as required

If this post helps click Yes
---------------
Jacob Skaria


"Visakha" wrote:

Hi Jacob,

Thanks for catching that--I meant before or on. And yes, this macro works
but I am wondering how to apply it a column of cells. Say the expiration
dates are listed in column b (reiterating to up to 1000). Is there a way to
show the assigned date in column C instead of a dialog box? I am fairly new
to macros but am enjoying them! Thanks so much!

"Jacob Skaria" wrote:

Hi Visakha

You example and description contradicts.. However both options are given
below. Test and feedback

Example: if expiration is on 10/19/09, then assign to Wednesday date before
or on 10/19/09. Cell to show 10/21/09.


Sub Macro2()
Dim myDate As Date
myDate = DateValue("10/14/2009")

MsgBox "Wednesday before or current: " & DateAdd("d", _
IIf(Weekday(myDate) < 4, -3, 4) - Weekday(myDate), myDate)

MsgBox "Wednesday after or current: " & DateAdd("d", _
IIf(Weekday(myDate) <= 4, 4, 11) - Weekday(myDate), myDate)

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Visakha" wrote:

I have a spreadsheet that list when authorizations expire. Before the
expiration date, the authorization must be renewed in a meeting that occurs
every Wednesday. Thus far, the assigning of the renewal meetings has been
done manually. Is the a macro or lookup function that does this
automatically?

Example: if expiration is on 10/19/09, then assign to Wednesday date before
or on 10/19/09. Cell to show 10/21/09.

Thanks in advance!