Macro to Enter Formula in a Cell

[Thomas M.]

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

 

[Tom Ogilvy]

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

 

[Thomas M.]

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