Excel 2003

I have the following two lines in a simple macro. The macro should put the
date for last Monday in the selected cell.

SubDays = (Weekday(Date) - vbMonday)
ActiveCell.Formula = "=Today()"

The obvious problem is that "=Today()" will put the current date in the
cell, instead of the date for last Monday. So I need to add a correction
factor, and SubDays (for "subtract days") is intended to make that
correction. Conceptually, the second line above should be like the
following:

ActiveCell.Formula = "=Today()-SubDays"

I know that this syntax does not work, but conceptually what would happen is
that on Tuesday one day would be subtracted, on Wednesday 2 days would be
subtracted, etc. The result would be that no matter what day of the week it
is, the date for the last Monday would always be placed in the active cell.
The macro then turns the formula in the active cell into a value.

I can create a variable in VBA that holds the correct date by inserting:
MyDate = Date - SubDays. But again, I cannot figure out how to get that
value written to the active cell.

Can someone show me the syntax to make this concept work?

--Tom

=TODAY()-(WEEKDAY(TODAY(),2)-1)

--
Regards,
Tom Ogilvy




"Thomas M." wrote in message
...
Excel 2003

I have the following two lines in a simple macro. The macro should put
the date for last Monday in the selected cell.

SubDays = (Weekday(Date) - vbMonday)
ActiveCell.Formula = "=Today()"

The obvious problem is that "=Today()" will put the current date in the
cell, instead of the date for last Monday. So I need to add a correction
factor, and SubDays (for "subtract days") is intended to make that
correction. Conceptually, the second line above should be like the
following:

ActiveCell.Formula = "=Today()-SubDays"

I know that this syntax does not work, but conceptually what would happen
is that on Tuesday one day would be subtracted, on Wednesday 2 days would
be subtracted, etc. The result would be that no matter what day of the
week it is, the date for the last Monday would always be placed in the
active cell. The macro then turns the formula in the active cell into a
value.

I can create a variable in VBA that holds the correct date by inserting:
MyDate = Date - SubDays. But again, I cannot figure out how to get that
value written to the active cell.

Can someone show me the syntax to make this concept work?

--Tom

Thanks! That works. Also, I noticed that the formula can be simplified
slightly third WEEKDAY return type. So...

=TODAY()-(WEEKDAY(TODAY(),3))

also works.

--Tom

"Tom Ogilvy" wrote in message
...
=TODAY()-(WEEKDAY(TODAY(),2)-1)

--
Regards,
Tom Ogilvy




"Thomas M." wrote in message
...
Excel 2003

I have the following two lines in a simple macro. The macro should put
the date for last Monday in the selected cell.

SubDays = (Weekday(Date) - vbMonday)
ActiveCell.Formula = "=Today()"

The obvious problem is that "=Today()" will put the current date in the
cell, instead of the date for last Monday. So I need to add a correction
factor, and SubDays (for "subtract days") is intended to make that
correction. Conceptually, the second line above should be like the
following:

ActiveCell.Formula = "=Today()-SubDays"

I know that this syntax does not work, but conceptually what would happen
is that on Tuesday one day would be subtracted, on Wednesday 2 days would
be subtracted, etc. The result would be that no matter what day of the
week it is, the date for the last Monday would always be placed in the
active cell. The macro then turns the formula in the active cell into a
value.

I can create a variable in VBA that holds the correct date by inserting:
MyDate = Date - SubDays. But again, I cannot figure out how to get that
value written to the active cell.

Can someone show me the syntax to make this concept work?

--Tom