1st part works; how to get an actual date dumped?

  • Thread starter Thread starter MitchellWMA
  • Start date Start date
M

MitchellWMA

I was able to put together 2 macros and am pleased with results. When
I click on a button that has the macro below assigned to it, it puts
the date for this week's Monday into L2 which shows the beginning of
the week and which then affects all the rest of the dates in the sheet
that are dependent on L2:

**********************************
Sub MondayStartDate()
ActiveSheet.Unprotect
Application.Goto Reference:="R2C12"
ActiveCell.FormulaR1C1 = _
"=2-WEEKDAY(TODAY())+TODAY()"
Range("L2").Select
ActiveSheet.Protect
End Sub
********************************

However, the formula is what gets dumped into L2.
i.e., L2 currently shows:

=2-WEEKDAY(TODAY())+TODAY()

rather than:

2008.12.29

I don't know how to get the formula to do the switch. How do we do
that please? thx
 
(Oops sorry, thought I was in the programming ng. Forgive wrong ng
post. I'll just leave it at that and not re-post in right ng. Hope
that's ok. thx)
 
One way:

Public Sub StartDate()
With ActiveSheet
.Unprotect
.Range("L2").Value = 2 - Weekday(Date) + Date
.Protect
End With
End Sub
 
Hi,

Sub MondayStartDate()
ActiveSheet.Unprotect
[L2].Value =2-WEEKDAY(Date())+Date()
ActiveSheet.Protect
End Sub

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Hi,

Sub MondayStartDate()
ActiveSheet.Unprotect
    [L2].Value =2-WEEKDAY(Date())+Date()
ActiveSheet.Protect
End Sub
Click to expand...

[snip]

Shane, hi! Is there any reason to use one formula over the other?
I'm curious as to the difference. thx
 
Back
Top