Solved VBA code to insert new row and copy from row above

May 9, 2015 at 12:00:50
Specs: Windows 7
Hi, I have 100 groups of data.

Group1: 10 rows of data in row 2 to 11 from ColA to G. Cell A2 to G11 have been filled up. Group2: 25 rows of data in row 12 to 36 from ColA to L.

(a) I wish to insert 1 empty row in between each group.
(b) For Group 1, I wish to copy and paste the data from the last row of each group (eg C11 to G11) into the next blank C12 to G12 automatically. A11 is the reference date (Mar2015) and B11 is a figure. I want A12 to contain the next period (Apr2015) and B12 to be blank. I will key in data in Col B (eg Cell B12).
(c) For Group 2, to copy and paste data from A36 to L36 into next blank A37 to L37 automatically. A36 is the reference date (2010) and M36 is a figure. I want to A37 to contain the next period (2011) and M37 to be blank. I will key in data in Column M (eg Cell M37).

This process will repeat for the rest of the 98 groups.

Within each group, it filled up different columns, each containing different number of rows.

Can I have the VBA code to perform the above? Thanks.


See More: VBA code to insert new row and copy from row above

Report •


✔ Best Answer
May 19, 2015 at 11:16:25
Before I address your specific issues, I would like to suggest that you review the material in this How To.

http://www.computing.net/howtos/sho...

If you are going to be using VBA to streamline your Excel tasks, it might help to learn some debugging techniques so that you can understand/troubleshoot VBA code that is offered in this forum or that you find on the web. There is a huge amount of VBA code out there and sometimes all it takes is a little tweaking to make it work for a specific task. By using the debugging techniques offered in the Tutorial, you may find yourself becoming fairly proficient in a short amount of time.

OK, as for your specific issues:

re: There was an error when I run the codes: Run time error 13.
When I click on Debug, the following codes are highlighted in yellow with an arrow:

  '*** To increment by 1 Year ***
       Cells(nxtRw, "A") = _
            Cells(nxtRw - 1, "A") + 1

It seems that the codes to increment the Column A date by 1 Year is not commented off. Can I confirm this?

You are correct, I did not comment out the "To increment by 1 Year" section. It was the last thing I tested, and I simply forgot to comment it out. Sorry.

However, I am not getting a runtime error when I run the code against your sample data and I think I know why. When I copy your sample data into a spreadsheet, the values in Column A are seen as full dates. Therefore adding a "1" to each date simply adds 1 day to the dates. Yes, that section is for adding a "Year" but in reality all it does is add "1" to whatever value is in the cell. Based on how Excel stores dates internally, adding 1 to a date adds 1 day, adding 2 adds 2 days, etc.

I will bet that if you changed the values you have in Column A to 4 digit years such as 2014 – or any number for that matter - the code would run fine and increment each value by 1.

Here is why I think you are getting that error:

My guess is that your "dates" are not really "dates" as far as Excel is concerned . My guess is that they are actually text values. That can occur if the data was imported from some other application, like a database application or an HTML web page. We see that sort of thing all the time. If the dates are really text as far as Excel is concerned, then you can't add 1 to them. That might also explain the EDATE error. (see my comment on that below). You will need to convert all of those text values to be dates in order for the code to work. There may be a leading or trailing space, or they might just be seen as text.

re: I tried to add a ' sign to comment off this part. There is no error after running the codes but:

1. The heading (row 1) is repeated

The heading is repeated because the code compares C2 to C1, finds that they do not match and therefore inserts/copies the row from above C2. I believe I have solved that issue by stopping the comparison at Row 3. Please verify that it no longer copies Row 1 in your workbook.

re: 2. The new inserted row consist of a repeated period from the previous row (which is correct since no instruction was given to increment by 1 period)

Correct. That is as expected since all "periodical increment" sections are now commented out.

re: 3. The last "group" should have a new row below it.

I believe I have fixed this by starting the comparison at the first blank row after your data. Since the cell in Column C of the row after your last piece of data is blank, the code sees that as a "change" in Column C and insert/copies the row from above.

re: Results: Run time error 1004 Unable to get the EDate property of the WorksheetFunvtion class

Please read the "Common Errors" section of the page found at the link below. Assuming that you have the Analysis ToolPak installed, my guess (as noted earlier) is that your dates aren't being seen as dates. You can test this by trying the EDATE function in a cell and see what happens.

=EDATE(A12,1)

If my assumption is correct, you will get a #VALUE error as described at the following site. If the Analysis ToolPak isn't installed, you'll get a #NAME error. Once the EDATE function works in a cell, it should work in VBA.

http://www.excelfunctions.net/Excel...

Try this version of the code and let me know how it works for you (once you resolve the date issue). It is currently set up to do yearly increments, as before.

Sub InsertRows()
'Determine last Row with Data in Column C
 lastRw = Cells(Rows.Count, "C").End(xlUp).Row
  
'Loop through rows in reverse order, looking for a change in Column C
  For nxtRw = lastRw + 1 To 3 Step -1
   If Cells(nxtRw - 1, "C") <> Cells(nxtRw, "C") Then
   
'If a change occurs, Copy & Insert Row
    Cells(nxtRw - 1, "C").EntireRow.Copy
    Cells(nxtRw - 1, "A").Insert
    
'*** To Increment by 1 Month ***
     'Cells(nxtRw, "A") = _
            Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), 1)
     
'*** To Increment by 1 Quarter (3 months) ***
     'Cells(nxtRw, "A") = _
            Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), 3)
     
'*** To increment by 1 Year ***
     Cells(nxtRw, "A") = _
            Cells(nxtRw - 1, "A") + 1
     
'Clear Column B
     Cells(nxtRw, "B") = ""
   End If
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 9, 2015 at 17:22:04
re: "Your Group 1 & 2 requirements"

This is easy to do since you supplied all of the details that the VBA code will need to accomplish the task.

Sub TheEasyPart()
'Copy Row 36, Insert Row 37
   Rows(36).EntireRow.Copy
   Rows(37).Insert shift:=xlDown
'Update Year, Clear B37
   Cells(37, 1) = "2011"
   Cells(37, 2) = ""
'Copy Row 11, Insert Row 12
   Rows(11).EntireRow.Copy
   Rows(12).Insert shift:=xlDown
'Update Date, Clear B12
   Cells(12, 1) = "April 2015"
   Cells(12, 2) = ""
End Sub

re: This process will repeat for the rest of the 98 groups.

Within each group, it filled up different columns, each containing different number of rows.

This is impossible to do because there is not enough detail for VBA to know where to insert the new Row. VBA can't "guess" as to where the each of the next 98 groups ends. In order for the code to insert a new row at the proper location, it needs something to key on.

Imagine if you asked a human to do exactly what you are asking the VBA code to do. If you sat a person down in front of your spreadsheet and simply told them to "Insert a new row at the end of each of the 100 groups", would they be able to do it? How would they know where to insert each new row?

Just like you would need to tell a person how to determine the end of each group, you have to tell VBA what to look for in order to know where to insert the new rows.

There needs to be something "common" at the end of each group that the code can recognize.

That said, there is one more element that (at least at this point) makes supplying VBA code a little tough. At the end of Group 1 you wanted the value in A11 incremented by 1 month. For Group 2 you wanted the value in A16 incremented by 1 year. 2 groups, 2 different values placed in the new Row. What will be placed in Column A for groups 3, 4, 5...98? Again, VBA can't guess, it must be told.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 10, 2015 at 01:11:40
Hi, Thanks very much for the prompt reply and useful advice.
The previous question I have posted is a simplified example of my actual requirement.
I would wish to modify my previous question by providing the actual requirements with more details:
I have x number of groups of data from Column A to G. Within each group, Column A contains the reference period and B is a figure. Column C to G contains the same data for the remaining rows. Each group contain different number of rows.
(a)I wish to insert 1 empty row in between each group.
(b)Within each group, after inserting the empty row, I wish to copy and paste the data from the previous row of each group into the new row from Col C to G automatically. Col A should contain the next reference period and B to be blank.
(c)In group 1, Column C=Brand XYZ and it is the common identifier throughout group 1. In group 2, Column C=Brand ABC and it is the common identifier throughout group 2. The same applies to the remaining groups. Each group contain different brand name.
(d)As column C is unique for each group, I want excel to automatically to loop through Group 1 based on Column C and move on to Group 2 and remaining groups automatically to perform the insertion of new rows and copy and paste the data from the previous row automatically.
Can I have the VBA code to perform the above? Thanks.

Report •

#3
May 10, 2015 at 04:10:26
re: "(d)As column C is unique for each group, ..."

We now understand that VBA can use the "change" in Column C as the indicator for the end of each group. In other words, if C23 <> C24, then Copy/Insert Row 23. Thank you.

re: "(b)Within each group, after inserting the empty row, I wish to copy and paste the data from the previous row of each group into the new row from Col C to G automatically..."

You've asked to insert a new row, but to only copy C:G. Should the code limit the copy to C:G or can the entire Row be copied? I only ask because there is there is slightly less code required to copy the entire row.

re: "...Col A should contain the next reference period and B to be blank."

How is the reference period designated? As I mentioned in my previous response, at the end of Group 1 you wanted the value in A11 incremented by 1 month. At the end of Group 2 you wanted the value in A36 incremented by 1 year. Does the code need to determine whether the Column A date is a "month and a year" or just a "year" and increment accordingly? Within reason, something like that can be done, it just takes more code. Once again before any code could be written, we would need to know various date formats that the "reference period" date might be in.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 10, 2015 at 05:36:56
Hi, Thanks for the reply.
The entire row can be copied. Col A has to be incremented by 1 period and B to be blank.
I can separate the data by frequency into 3 worksheets: Month, Quarter and year. Hence, the code need not determine the frequency of col A. I can run the worksheets separately.
Thanks.


Report •

#5
May 11, 2015 at 09:51:19
The following code should do what you have asked for. I have included the instructions required to increment the Column A date by 1 Month, 1 Quarter (3 months) and 1 Year. Each of the instructions is currenty commented out so nothing will happen to the value in Column A unless you pick one an instruction and delete the leading single quote.

Sub InsertRows()
'Determine last Row with Data in Column C
 lastRw = Cells(Rows.Count, "C").End(xlUp).Row
 
'Loop through rows in reverse order, looking for a change in Column C
  For nxtRw = lastRw To 2 Step -1
   If Cells(nxtRw - 1, "C") <> Cells(nxtRw, "C") Then
   
'If a change occurs, Copy & Insert Row
    Cells(nxtRw - 1, "C").EntireRow.Copy
    Cells(nxtRw - 1, "A").Insert
    
'*** To Increment by 1 Month ***
     'Cells(nxtRw, "A") = _
            Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), 1)
     
'*** To Increment by 1 Quarter (3 months) ***
     'Cells(nxtRw, "A") = _
            Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), 3)
     
'*** To increment by 1 Year ***
     Cells(nxtRw, "A") = _
            Cells(nxtRw - 1, "A") + 1
     
'Clear Column B
     Cells(nxtRw, "B") = ""
   End If
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
May 11, 2015 at 12:07:04
BTW...if there was something that the code could key off of to know whether to increment by a month, a quarter or a year, then you wouldn’t need 3 separate sheets.

For example if your Year only cells are 4 digits, then the code could easily check that and increment any "4 digit cell" by 1.

Where it gets tricky is Quarterly vs. Monthly increments since both of those dates would (I assume) be formatted the same (mm/dd/yyyy). Perhaps specific months could indicate Quarters.

If there was something else within each group that could be used to tell the code that the increment should be a quarter vs. a month that “key” could be written into the code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 17, 2015 at 06:42:42
Hi,

Thanks for the VBA codes. Very sorry for the late reply.

I have provided the sample data (annual) below:


Period (Year) Sales Brand Model Serial No. Colour Size
01/01/1960 100 ABC XYZ 123456 Blue S
01/01/1961 200 ABC XYZ 123456 Blue S
01/01/1960 500 DEF abc 789101 Red M
01/01/1961 1000 DEF mno 789101 Red M
01/01/1962 1500 DEF rte 12345 Blue S
01/01/1960 300 RSQ IJK 890123 Green L
01/01/1961 600 RSQ abc 678901 Yellow S
01/01/1960 500 XYZ XYZ 123456 Blue S


There was an error when I run the codes: Run time error 13.
When I click on Debug, the following codes are highlighted in yellow with an arrow:

'*** To increment by 1 Year ***
Cells(nxtRw, "A") = _
Cells(nxtRw - 1, "A") + 1

It seems that the codes to increment the Column A date by 1 Year is not commented off. Can I confirm this?

I tried to add a ' sign to comment off this part. There is no error after running the codes but:
1. The heading (row 1) is repeated
2. The new inserted row consist of a repeated period from the previous row (which is correct since no instruction was given to increment by 1 period)
3. The last "group" should have a new row below it.
Attached the results below for your reference:


Period (Year) Sales Brand Model Serial No. Colour Size
Period (Year) Brand Model Serial No. Colour Size
01/01/1960 100 ABC XYZ 123456 Blue S
01/01/1961 200 ABC XYZ 123456 Blue S
01/01/1961 ABC XYZ 123456 Blue S
01/01/1960 500 DEF abc 789101 Red M
01/01/1961 1000 DEF mno 789101 Red M
01/01/1962 1500 DEF rte 12345 Blue S
01/01/1962 DEF rte 12345 Blue S
01/01/1960 300 RSQ IJK 890123 Green L
01/01/1961 600 RSQ abc 678901 Yellow S
01/01/1961 RSQ abc 678901 Yellow S
01/01/1960 500 XYZ XYZ 123456 Blue S

I also tried running the following:

Monthly sample data:


Period (Year) Sales Brand Model Serial No. Colour Size
01/31/1975 100 ABC XYZ 123456 Blue S
02/28/1975 200 ABC XYZ 123456 Blue S
01/31/1975 500 DEF abc 789101 Red M
02/28/1975 1000 DEF mno 789101 Red M
03/31/1975 1500 DEF rte 12345 Blue S
01/31/1975 300 RSQ IJK 890123 Green L
02/28/1975 600 RSQ abc 678901 Yellow S
01/31/1975 500 XYZ XYZ 123456 Blue S

Results: Run time error 1004 Unable to get the EDate property of the WorksheetFunvtion class


Period (Year) Sales Brand Model Serial No. Colour Size
Period (Year) Sales Brand Model Serial No. Colour Size
01/31/1975 100 ABC XYZ 123456 Blue S
02/28/1975 200 ABC XYZ 123456 Blue S
27481 ABC XYZ 123456 Blue S
01/31/1975 500 DEF abc 789101 Red M
02/28/1975 1000 DEF mno 789101 Red M
03/31/1975 1500 DEF rte 12345 Blue S
04/30/1975 DEF rte 12345 Blue S
01/31/1975 300 RSQ IJK 890123 Green L
02/28/1975 600 RSQ abc 678901 Yellow S
27481 RSQ abc 678901 Yellow S
01/31/1975 500 XYZ XYZ 123456 Blue S

Quarterly sample data:


Period (Year) Sales Brand Model Serial No. Colour Size
03/31/1975 100 ABC XYZ 123456 Blue S
06/30/1975 200 ABC XYZ 123456 Blue S
03/31/1975 500 DEF abc 789101 Red M
06/30/1975 1000 DEF mno 789101 Red M
09/30/1975 1500 DEF rte 12345 Blue S
03/31/1975 300 RSQ IJK 890123 Green L
06/30/1975 600 RSQ abc 678901 Yellow S
03/31/1975 500 XYZ XYZ 123456 Blue S

Results:Run time error 1004 Unable to get the EDate property of the WorksheetFunvtion class


Period (Year) Sales Brand Model Serial No. Colour Size
Period (Year) Sales Brand Model Serial No. Colour Size
03/31/1975 100 ABC XYZ 123456 Blue S
06/30/1975 200 ABC XYZ 123456 Blue S
27667 ABC XYZ 123456 Blue S
03/31/1975 500 DEF abc 789101 Red M
06/30/1975 1000 DEF mno 789101 Red M
09/30/1975 1500 DEF rte 12345 Blue S
27758 DEF rte 12345 Blue S
03/31/1975 300 RSQ IJK 890123 Green L
06/30/1975 600 RSQ abc 678901 Yellow S
27667 RSQ abc 678901 Yellow S
03/31/1975 500 XYZ XYZ 123456 Blue S

The date are not correctly reflect and there are runtime errors. Would appreciate your advise, please. Thanks for your assistance rendered so far.


Report •

#8
May 17, 2015 at 06:47:02
Hi,
Thanks for the VBA codes. Very sorry for the late reply.
I have provided the sample data (annual) below:

Period (Year) Sales Brand Model Serial No. Colour Size
01/01/1960 100 ABC XYZ 123456 Blue S
01/01/1961 200 ABC XYZ 123456 Blue S
01/01/1960 500 DEF abc 789101 Red M
01/01/1961 1000 DEF mno 789101 Red M
01/01/1962 1500 DEF rte 12345 Blue S
01/01/1960 300 RSQ IJK 890123 Green L
01/01/1961 600 RSQ abc 678901 Yellow S
01/01/1960 500 XYZ XYZ 123456 Blue S

Report •

#9
May 17, 2015 at 06:47:47
Sorry, please ignore my 2nd posting at 6:47:02.

Report •

#10
May 17, 2015 at 06:54:31
Hi, Please refer to my reply below.
Thanks for the VBA codes. Very sorry for the late reply.
I have provided the sample data (annual) below:
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size
01/01/1960	100	ABC	XYZ	123456	Blue	S
01/01/1961	200	ABC	XYZ	123456	Blue	S
01/01/1960	500	DEF	abc	789101	Red	M
01/01/1961	1000	DEF	mno	789101	Red	M
01/01/1962	1500	DEF	rte	12345	Blue	S
01/01/1960	300	RSQ	IJK	890123	Green	L
01/01/1961	600	RSQ	abc	678901	Yellow	S
01/01/1960	500	XYZ	XYZ	123456	Blue	S

There was an error when I run the codes: Run time error 13.
When I click on Debug, the following codes are highlighted in yellow with an arrow:
'*** To increment by 1 Year ***
Cells(nxtRw, "A") = _
Cells(nxtRw - 1, "A") + 1

It seems that the codes to increment the Column A date by 1 Year is not commented off. Can I confirm this?
I tried to add a ' sign to comment off this part. There is no error after running the codes but:
1. The heading (row 1) is repeated
2. The new inserted row consist of a repeated period from the previous row (which is correct since no instruction was given to increment by 1 period)
3. The last "group" should have a new row below it.
Attached the results below for your reference:
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size
Period (Year)		Brand	Model	Serial No.	Colour	Size
01/01/1960	100	ABC	XYZ	123456	Blue	S
01/01/1961	200	ABC	XYZ	123456	Blue	S
01/01/1961		ABC	XYZ	123456	Blue	S
01/01/1960	500	DEF	abc	789101	Red	M
01/01/1961	1000	DEF	mno	789101	Red	M
01/01/1962	1500	DEF	rte	12345	Blue	S
01/01/1962		DEF	rte	12345	Blue	S
01/01/1960	300	RSQ	IJK	890123	Green	L
01/01/1961	600	RSQ	abc	678901	Yellow	S
01/01/1961		RSQ	abc	678901	Yellow	S
01/01/1960	500	XYZ	XYZ	123456	Blue	S

I also tried running the following:
Monthly sample data:
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size
01/31/1975	100	ABC	XYZ	123456	Blue	S
02/28/1975	200	ABC	XYZ	123456	Blue	S
01/31/1975	500	DEF	abc	789101	Red	M
02/28/1975	1000	DEF	mno	789101	Red	M
03/31/1975	1500	DEF	rte	12345	Blue	S
01/31/1975	300	RSQ	IJK	890123	Green	L
02/28/1975	600	RSQ	abc	678901	Yellow	S
01/31/1975	500	XYZ	XYZ	123456	Blue	S

Results: Run time error 1004 Unable to get the EDate property of the WorksheetFunvtion class:
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size
01/31/1975	100	ABC	XYZ	123456	Blue	S
02/28/1975	200	ABC	XYZ	123456	Blue	S
27481		ABC	XYZ	123456	Blue	S
01/31/1975	500	DEF	abc	789101	Red	M
02/28/1975	1000	DEF	mno	789101	Red	M
03/31/1975	1500	DEF	rte	12345	Blue	S
04/30/1975		DEF	rte	12345	Blue	S
01/31/1975	300	RSQ	IJK	890123	Green	L
02/28/1975	600	RSQ	abc	678901	Yellow	S
27481		RSQ	abc	678901	Yellow	S
01/31/1975	500	XYZ	XYZ	123456	Blue	S

Quarterly sample data:
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size
03/31/1975	100	ABC	XYZ	123456	Blue	S
06/30/1975	200	ABC	XYZ	123456	Blue	S
03/31/1975	500	DEF	abc	789101	Red	M
06/30/1975	1000	DEF	mno	789101	Red	M
09/30/1975	1500	DEF	rte	12345	Blue	S
03/31/1975	300	RSQ	IJK	890123	Green	L
06/30/1975	600	RSQ	abc	678901	Yellow	S
03/31/1975	500	XYZ	XYZ	123456	Blue	S

Results:Run time error 1004 Unable to get the EDate property of the WorksheetFunvtion class
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size
03/31/1975	100	ABC	XYZ	123456	Blue	S
06/30/1975	200	ABC	XYZ	123456	Blue	S
27667		ABC	XYZ	123456	Blue	S
03/31/1975	500	DEF	abc	789101	Red	M
06/30/1975	1000	DEF	mno	789101	Red	M
09/30/1975	1500	DEF	rte	12345	Blue	S
27758		DEF	rte	12345	Blue	S
03/31/1975	300	RSQ	IJK	890123	Green	L
06/30/1975	600	RSQ	abc	678901	Yellow	S
27667		RSQ	abc	678901	Yellow	S
03/31/1975	500	XYZ	XYZ	123456	Blue	S

The dates are not correctly reflected and there are runtime errors. Would appreciate your advise, please. Thanks for your assistance rendered so far.


Report •

#11
May 19, 2015 at 11:16:25
✔ Best Answer
Before I address your specific issues, I would like to suggest that you review the material in this How To.

http://www.computing.net/howtos/sho...

If you are going to be using VBA to streamline your Excel tasks, it might help to learn some debugging techniques so that you can understand/troubleshoot VBA code that is offered in this forum or that you find on the web. There is a huge amount of VBA code out there and sometimes all it takes is a little tweaking to make it work for a specific task. By using the debugging techniques offered in the Tutorial, you may find yourself becoming fairly proficient in a short amount of time.

OK, as for your specific issues:

re: There was an error when I run the codes: Run time error 13.
When I click on Debug, the following codes are highlighted in yellow with an arrow:

  '*** To increment by 1 Year ***
       Cells(nxtRw, "A") = _
            Cells(nxtRw - 1, "A") + 1

It seems that the codes to increment the Column A date by 1 Year is not commented off. Can I confirm this?

You are correct, I did not comment out the "To increment by 1 Year" section. It was the last thing I tested, and I simply forgot to comment it out. Sorry.

However, I am not getting a runtime error when I run the code against your sample data and I think I know why. When I copy your sample data into a spreadsheet, the values in Column A are seen as full dates. Therefore adding a "1" to each date simply adds 1 day to the dates. Yes, that section is for adding a "Year" but in reality all it does is add "1" to whatever value is in the cell. Based on how Excel stores dates internally, adding 1 to a date adds 1 day, adding 2 adds 2 days, etc.

I will bet that if you changed the values you have in Column A to 4 digit years such as 2014 – or any number for that matter - the code would run fine and increment each value by 1.

Here is why I think you are getting that error:

My guess is that your "dates" are not really "dates" as far as Excel is concerned . My guess is that they are actually text values. That can occur if the data was imported from some other application, like a database application or an HTML web page. We see that sort of thing all the time. If the dates are really text as far as Excel is concerned, then you can't add 1 to them. That might also explain the EDATE error. (see my comment on that below). You will need to convert all of those text values to be dates in order for the code to work. There may be a leading or trailing space, or they might just be seen as text.

re: I tried to add a ' sign to comment off this part. There is no error after running the codes but:

1. The heading (row 1) is repeated

The heading is repeated because the code compares C2 to C1, finds that they do not match and therefore inserts/copies the row from above C2. I believe I have solved that issue by stopping the comparison at Row 3. Please verify that it no longer copies Row 1 in your workbook.

re: 2. The new inserted row consist of a repeated period from the previous row (which is correct since no instruction was given to increment by 1 period)

Correct. That is as expected since all "periodical increment" sections are now commented out.

re: 3. The last "group" should have a new row below it.

I believe I have fixed this by starting the comparison at the first blank row after your data. Since the cell in Column C of the row after your last piece of data is blank, the code sees that as a "change" in Column C and insert/copies the row from above.

re: Results: Run time error 1004 Unable to get the EDate property of the WorksheetFunvtion class

Please read the "Common Errors" section of the page found at the link below. Assuming that you have the Analysis ToolPak installed, my guess (as noted earlier) is that your dates aren't being seen as dates. You can test this by trying the EDATE function in a cell and see what happens.

=EDATE(A12,1)

If my assumption is correct, you will get a #VALUE error as described at the following site. If the Analysis ToolPak isn't installed, you'll get a #NAME error. Once the EDATE function works in a cell, it should work in VBA.

http://www.excelfunctions.net/Excel...

Try this version of the code and let me know how it works for you (once you resolve the date issue). It is currently set up to do yearly increments, as before.

Sub InsertRows()
'Determine last Row with Data in Column C
 lastRw = Cells(Rows.Count, "C").End(xlUp).Row
  
'Loop through rows in reverse order, looking for a change in Column C
  For nxtRw = lastRw + 1 To 3 Step -1
   If Cells(nxtRw - 1, "C") <> Cells(nxtRw, "C") Then
   
'If a change occurs, Copy & Insert Row
    Cells(nxtRw - 1, "C").EntireRow.Copy
    Cells(nxtRw - 1, "A").Insert
    
'*** To Increment by 1 Month ***
     'Cells(nxtRw, "A") = _
            Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), 1)
     
'*** To Increment by 1 Quarter (3 months) ***
     'Cells(nxtRw, "A") = _
            Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), 3)
     
'*** To increment by 1 Year ***
     Cells(nxtRw, "A") = _
            Cells(nxtRw - 1, "A") + 1
     
'Clear Column B
     Cells(nxtRw, "B") = ""
   End If
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
May 30, 2015 at 12:30:42
Hi, Sorry for the late reply.
Thanks for your valuable advice. All dates are converted to the format of dd/mm/yyyy to as the last day of each reference period (e.g month:31/1/1975,Quarter:31/3/1975, annual: 31/12/1960) and all the codes run successfully.

I have another set of data. I have x number of groups of data from Column A to G. Within each group, Column A contains the reference period and B is a figure. Column C to G contains the same data for the remaining rows. Each group contain different number of rows. I have included a new column H to indicate the number of time period for each group. Data are in month, quarter, annual frequency similar to the previous example.
Sample annual data:below:

Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size	No. of periods
31/12/1960	100	ABC	XYZ	123456	Blue	S	6
31/12/1961	200	ABC	XYZ	123456	Blue	S	6
31/12/1988	500	DEF	abc	789101	Red	M	10
31/12/1989	1000	DEF	mno	789101	Red	M	10
31/12/1990	1500	DEF	rte	12345	Blue	S	10
31/12/1975	300	RSQ	IJK	890123	Green	L	26
31/12/1976	600	RSQ	abc	678901	Yellow	S	26
31/12/2005	500	XYZ	XYZ	123456	Blue	S	8

Similar to the previous dataset, the common identifier within each group of data is Col C(Brand). For Brand=ABC, it should contain 6 time period. Row2 and 3 consist of the existing data for 1960 & 1961.
I wish to insert 4 new rows below and copy the data & paste from the previous row. Col A should contain the next 4 reference period and B to be blank.

My requirements are the same as the previous example. Except the number of new rows inserted is not fixed but should be based on Col H (number of time period) minus existing time period in within each group.

I will be also updating Col H daily to check for a change in number of time period. The code will be run on daily basis. If there is no change in time period, no new rows should be inserted or copied.

Can I have the VBA code to perform the above? Thanks.

message edited by lhm


Report •

#13
May 31, 2015 at 18:47:54
Now that your previous question has been answered and the thread has been marked as solved, it should not be used for other questions. The reason for this policy is to keep the archives organized and searchable.

If you need help with a different issue, please start a new thread with a relevant subject line.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
June 3, 2015 at 01:14:37
Hi, Noted the above. Thanks.

Report •


Ask Question