How to date stamp last Sunday's date

Question

I'm looking to have a couple of dates stamped onto the spreadsheet on opening.

My current script is as follows:

Private Sub Workbook_Open()

'Automate start time on opening
    With Worksheets("Time Recording Estimate")
        If IsEmpty(.Range("B4")) Then .Range("B4").Value = "Start"
    End With
End Sub

I now wish to also add a stamp for the last Sunday, hoping it can look something like the following:

Private Sub Workbook_Open()

'Automate start time on opening
    With Worksheets("Time Recording Estimate")
        If IsEmpty(.Range("B4")) Then 
            .Range("B4").Value = "Start"
            .Range("V3").Value = Now() - Weekday(Now()) + 1
    End With
End Sub

I'm not sure which part of the formula is wrong and is failing to go through.

Much appreciated for any assistance offered.

So, what's going wrong? Are you getting an error? It's good that you included your code - and it's also a good idea to minimize it to the part that's the problem, when possble. (ie, if it's just *miscalculating*, then you only need to include the formula.) More info on that at: [mcve] — ashleedawg, Feb 19 '18 at 00:23
@ashleedawg, I'm getting a highlight on the `Private Sub Workbook_Open()` line... and the script won't run at all... — kitarika, Feb 19 '18 at 00:25
when it stops on will the yellow highlighted line (that a break in code), what happens when you push F5 (to continue running the macro) — ashleedawg, Feb 19 '18 at 00:28
@ashleedawg, it worked... I've left out the `Else` at the end of the 2 commands... thanks so much for your time and helping me through this! — kitarika, Feb 19 '18 at 00:35
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/165382/discussion-between-kitarika-and-ashleedawg). — kitarika, Feb 19 '18 at 00:48

score 1 · Accepted Answer · answered Feb 19 '18 at 00:37

Looks like your code is missing an End If:

Private Sub Workbook_Open()

'Automate start time on opening
    With Worksheets("Time Recording Estimate")
        If IsEmpty(.Range("B4")) Then
            .Range("B4").Value = "Start"
            .Range("V3").Value = Now() - Weekday(Now()) + 1
        End If
    End With
End Sub

Once I added that (and changed the worksheet name to one that I actually have), it ran properly: if cell B4 is empty then then it puts "Start" in cell B4 and Sunday's date (today in this case, since it's Sunday) in cell V3.

I'd suggest that you [always] add this line to the top of any module:

Option Explicit

This will "force" proper declaration & usage of variables, objects, properties, etc, and will save you a lot of headaches in the future.

Also, you should compile the code, so you can see where problem lie. Here is a quick overview of the process.

How to date stamp last Sunday's date

1 Answers1