We help IT Professionals succeed at work.

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

Edward Pamias
on
118 Views
Last Modified: 2014-05-22
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
Comment
Watch Question

Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
I need the weekending date in the inserted line.
CERTIFIED EXPERT
Top Expert 2016

Commented:
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 PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
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...
CERTIFIED EXPERT
Top Expert 2016

Commented:
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 PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
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,
CERTIFIED EXPERT
Top Expert 2016

Commented:
I'm not sure to understand
CERTIFIED EXPERT
Top Expert 2016

Commented:
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 PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
the macro here gives me this Fridays date.... but I need it to give me last Fridays date.
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
Ok.... that worked.... I removed -7 because that made it go back 2 weeks.... this is great thanks!!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.