Avatar of Edward Pamias
Edward Pamias
Flag 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
Spreadsheets

Avatar of undefined
Last Comment
Edward Pamias

8/22/2022 - Mon
Edward Pamias

ASKER
I need the weekending date in the inserted line.
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
Edward Pamias

ASKER
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...
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rgonzo1971

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

Edward Pamias

ASKER
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,
Rgonzo1971

I'm not sure to understand
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rgonzo1971

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

Edward Pamias

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Edward Pamias

ASKER
Ok.... that worked.... I removed -7 because that made it go back 2 weeks.... this is great thanks!!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes