Modify VBA to suit

[Papi]

Below is code from a Microsoft site that creates new rows each time it sees a number eg. if the number is 12 it will generate an additional 11 rows. Everything works great but I need to add the number 1 to each cell in column E when it creates each new row. If the number is 1 it will populate to 2 and then to 3 etc. How can this be modified to accomplish this?

Public Sub InsBelow()
Dim lngStartRow As Long
Dim lngEndRow As Long
Dim m As Integer
Dim n As Long
With ActiveSheet
lngStartRow = Range("F" & CStr(Application.Rows.Count)).End(xlUp).Row
lngEndRow = 1
For n = lngStartRow To lngEndRow Step -1
If IsNumeric(Range("F" & CStr(n)).Value) And Range("F" & CStr(n)).Value <> "" Then
    Rows(n + 1).Resize(Range("F" & n).Value - 1).Insert
     Rows(n).Resize(Range("F" & n).Value).FillDown
        End If
    Next n
End With
End Sub

 

[Joe4]

Can you lay out an example of what the data looks like a sheet it would run against so we can see exactly what the code does when it runs (it depends on what the data and layout of the sheet looks like)?

 

[Papi]

Thanks Joe. Hopefully this works as we cannot put addins on our work pc's
Below is what it starts with...

Purchase Order Part No Cost Date Period Require Rows Code 1
PO 23456 16402-2 18.53 06/02/14 1 3 A3
PO 23456 5H1626 0.53 06/03/14 2 3 A7
PO 23456 3N8821 1.87 06/03/14 2 4 J6
PO 23456 7Z8025 2.65 06/04/14 7 3 W42
PO 23456 1045 12.02 06/04/14 4 6 A4


Modified

Purchase Order Part No Cost Date Period Require Rows Code 1
PO 23456 16402-2 18.53 06/02/14 1 3 A3
PO 23456 16402-2 18.53 06/02/14 2 3 A3
PO 23456 16402-2 18.53 06/02/14 3 3 A3
PO 23456 5H1626 0.53 06/03/14 2 3 A7
PO 23456 5H1626 0.53 06/03/14 3 3 A7
PO 23456 5H1626 0.53 06/03/14 4 3 A7
PO 23456 3N8821 1.87 06/03/14 2 4 J6
PO 23456 3N8821 1.87 06/03/14 3 4 J6
PO 23456 3N8821 1.87 06/03/14 4 4 J6
PO 23456 3N8821 1.87 06/03/14 5 4 J6
PO 23456 7Z8025 2.65 06/04/14 7 3 W42
PO 23456 7Z8025 2.65 06/04/14 8 3 W42
PO 23456 7Z8025 2.65 06/04/14 9 3 W42
PO 23456 1045 12.02 06/04/14 4 6 A4
PO 23456 1045 12.02 06/04/14 5 6 A4
PO 23456 1045 12.02 06/04/14 6 6 A4
PO 23456 1045 12.02 06/04/14 7 6 A4
PO 23456 1045 12.02 06/04/14 8 6 A4
PO 23456 1045 12.02 06/04/14 9 6 A4

 

[Joe4]

I cannot use add-ins either, so I sometimes use Code Tags to lay-out the data, i.e.

A        B       C
Name     Age     Gender
Bob      25      Male
Cindy    29      Female
Tom      44      Male

It takes a little playing around (use Print Preview and keep going back and forth until things line-up).

Or, you can you just tell me what column each field resides in (I can't be certain where one field starts and another ends, whether you are starting in column A or some other column, or if there are any blank columns in the middle of your data)?

Thanks

 

[Papi]

Sorry Joe. It looked fine until I sent it. Let me try your first method. Thanks

 

[Papi]

Column A PO Number
Column B Part Number
Column C Cost
Column D Date
Column E Period
Column F Rows Required
Column G Code

Column A:D Copy
Column E goes up by one each
Column F indicates the number of required rows to replicate
Column G copies

Does this make sense on the top piece?

 

[Papi]

The following is for column E starting from row 2 and going up by one increment each.
1
2
3
2
3
4
2
3
4
5
7
8
9
4
5
6
7
8
9

 

[Papi]

I am not clear on using the code tags. Are they the same ones like

 ?

 

[Joe4]

Yes, and you use /Code in square brackets to close them off.
Or, just use the code brackets in the editor (looks like a pound sign).

 

[Papi]

Thank you for your patience Joe.

******** language="JavaScript" ************************************************************************>

Purchase Register.xlsm
	A	B	C	D	E	F	G
1	PurchaseOrder	PartNo.	Cost	Date	Period	Rows	Code
2	PO23456	16402-2	18.53	2006-02-14	1	3	A3
3	PO23456	5H1626	0.53	2006-03-14	2	3	A7
4	PO23456	3N8821	1.87	2006-03-14	2	4	J6
5	PO23456	7Z8025	2.65	2006-04-14	7	3	W42
6	PO23456	1045	12.02	2006-04-14	4	6	A4
7
8	Modified
9
10	PurchaseOrder	PartNo.	Cost	Date	Period	Rows	Code
11	PO23456	16402-2	18.53	2006-02-14	1	3	A3
12	PO23456	16402-2	18.53	2006-02-14	2	3	A3
13	PO23456	16402-2	18.53	2006-02-14	3	3	A3
14	PO23456	5H1626	0.53	2006-03-14	2	3	A7
15	PO23456	5H1626	0.53	2006-03-14	3	3	A7
16	PO23456	5H1626	0.53	2006-03-14	4	3	A7
17	PO23456	3N8821	1.87	2006-03-14	2	4	J6
18	PO23456	3N8821	1.87	2006-03-14	3	4	J6
19	PO23456	3N8821	1.87	2006-03-14	4	4	J6
20	PO23456	3N8821	1.87	2006-03-14	5	4	J6
21	PO23456	7Z8025	2.65	2006-04-14	7	3	W42
22	PO23456	7Z8025	2.65	2006-04-14	8	3	W42
23	PO23456	7Z8025	2.65	2006-04-14	9	3	W42
24	PO23456	1045	12.02	2006-04-14	4	6	A4
25	PO23456	1045	12.02	2006-04-14	5	6	A4
26	PO23456	1045	12.02	2006-04-14	6	6	A4
27	PO23456	1045	12.02	2006-04-14	7	6	A4
28	PO23456	1045	12.02	2006-04-14	8	6	A4
29	PO23456	1045	12.02	2006-04-14	9	6	A4
Sheet1