Excel - Macro copy Date for "n" month

Solved/Closed
mama_tracy Posts 3 Registration date Friday May 14, 2010 Status Member Last seen May 18, 2010 - May 16, 2010 at 10:48 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 18, 2010 at 02:41 AM
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


Related:

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 17, 2010 at 05:56 AM
5
mama_tracy Posts 3 Registration date Friday May 14, 2010 Status Member Last seen May 18, 2010
May 18, 2010 at 12:47 AM
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)).
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 18, 2010 at 02:41 AM
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
0