vba - If Monday then add x y z

Question

Can you please advise how to restructure my code. At the moment I am adding a date -1 to the bottom of the column D (every day). However, when it's Monday I need to add the date -3, then date -2 and then date -1 to the bottom of the column D.

Here is my current code:

y.Activate
With y1
Dim d As Long
d = Cells(Rows.Count, "D").End(xlUp).Row + 1 'there was an error in the code, I have changed the N to d
Cells(d, "D").Value = Date - 1
End With

My assumption would be the IIf statement? But then again I guess it requires a loop as well.

Thanks

标签： vbaexcel

Answer 1

Right before your Cells(d, "D").Value = Date - 1 line, throw in a:

If Weekday(Now()) = 2 Then
    Cells(d, "D").Value = Date - 3
    Cells(d + 1, "D").Value = Date - 2
    Cells(d + 2, "D").Value = Date - 1
Else
    Cells(d, "D").Value = Date - 1
End If

The If Weekday(Now()) will compare the day of the week (1 is Sunday by default I believe). So by checking to see if it's "2" then you are able to run what you'd like. I'm not sure if this is exactly what you were aiming for, but this will place three different date values, starting from Friday and ending at Sunday.

Probably not the best solution, but this was my understanding of the question. If I'm off let me know and I'll adjust my answer.