Link to home
Start Free TrialLog in
Avatar of Edward Pamias
Edward PamiasFlag for United States of America

asked on

How do I add a date to an inserted line using a macro.

I have a macro that inserts a line but I need to add the weekending date of the previous week after it inserts the line. Column A is where the date would be located. See the macro below to insert a line.

ActiveSheet.Range("A:A").Find("Total To Date", , , xlWhole).EntireRow.Insert
Avatar of Edward Pamias
Edward Pamias
Flag of United States of America image

ASKER

I need the weekending date in the inserted line.
Avatar of Rgonzo1971
Rgonzo1971

Hi,

Do you mean the Sunday before today?

ActiveSheet.Range("A:A").Find("Total To Date", , , xlWhole).EntireRow.Insert
ActiveSheet.Range("A:A").Find("Total To Date", , , xlWhole).Offset(-1).Value = Date - ((Date - 1) Mod 7)

Open in new window

Regards
I need Fridays date from last week. What I do is add raw data on one tab then I insert a line on my table and add last Fridays date...
pls try

ActiveSheet.Range("A:A").Find("Total To Date", , , xlWhole).EntireRow.Insert
ActiveSheet.Range("A:A").Find("Total To Date", , , xlWhole).Offset(-1).Value = Date - ((Date + 2) Mod 7)

Open in new window

This is great is there anyway to add last Fridays date? I add the data for the week before so I need weekending for the week before. Thanks,
I'm not sure to understand
HI

for Fridays

ActiveSheet.Range("A:A").Find("Total To Date", , , xlWhole).EntireRow.Insert
ActiveSheet.Range("A:A").Find("Total To Date", , , xlWhole).Offset(-1).Value = Date - ((Date + 1) Mod 7)

Open in new window

the macro here gives me this Fridays date.... but I need it to give me last Fridays date.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok.... that worked.... I removed -7 because that made it go back 2 weeks.... this is great thanks!!