Previous Topic Next Topic

Excel - Macro copy Date for "n" month

Solved/Closed
Report
Posts
3
Registration date
Friday May 14, 2010
Status
Member
Last seen
May 18, 2010
 -
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello,

Would you please help me to create a macro for generate Date schedule to Sheet2

It just like an installment schedule.


Sheet1
Col A is the detail of the installment, the value will not change by the amount of Col B
Col B is the terms of installment, so it will be the copy times of the rows
Col C is the start date of the installment. So copy to sheet2, the 1st row will be the Date value from Sheet1. But if sheet1 Col B = 2 and Col C = 01/09/2008, sheet2 B1 will show 01/09/2008 and B2 01/10/2008.


Let's see the example:


Sheet1

(Name) (Month) (Date:DD/MM/YYYY)
Col A Col B Col C
1 AAA 2 01/09/2008
2 BBB 5 01/01/2010


Sheet2

(Name) (Date:DD/MM/YYYY)
Col A Col B
1 AAA 01/09/2008
2 AAA 01/10/2008
3 BBB 01/01/2010
4 BBB 01/02/2010
5 BBB 01/03/2010
6 BBB 01/04/2010
7 BBB 01/05/2010


1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
See this
https://ccm.net/forum/affich-347407-excel-copy-rows-n-times-with-calculation

It only need slight modification
5
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Report
Posts
3
Registration date
Friday May 14, 2010
Status
Member
Last seen
May 18, 2010
I know my two requests are very very similar. But can you teach me how to change the month in VBA? I only know the formula for change the month in excel =DATE(YEAR(B1),MONTH(B1)+1,DAY(B1)).
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
There are more than one way you can do it. one way would be that before you start the macro, you convert it to the date. In fact if you use excel macro recorder and do it once, it will create a macro for you and then you can call that macro.

Other way would be to use CDATE

an other way would be to use formula

with range("C1:C19")

,formulaR1C1 = "=DATE(YEAR(RC2),MONTH(RC2)+1, DAY(RC2))

end with
Ask a question