Help with copy past under blank row.

[Harley78]

Hello All,

I am trying to figure out how to copy and paste formulas in column G & H starting on row 11. I currently copy and paste G7, H7 & G8, H8 manual down the list in those that have two or more rows, then double click so it populates down to the end of that section (Accumulative for that section). Then i go back and copy and paste G7 & H7 manually to those that contain only one line.

I have a macro that inserts blank rows (command button) between different part numbers (based on column A). Just wanted to see if there is a way i can do the above.

Is there a possibility? Any help or direction is appreciated

Thanks a bunch

Bill


Example:

Part Number Planner ICU REQ Stock Start time INV INV
after plus
after plus

build rec
A B C D E F G H
7 1-2-3-4 CD2 9-8-7-6 454 8767 08.07.2013 19:18:02 8,313 8,313 (G7 FORMULA = =E7-D7) (H7 FORMULA = =G7+(I7+J7+K7+L7+M7+N7+O7+P7+Q7+R7)
8 1-2-3-4 CD2 9-8-7-6 1176 09.07.2013 10:18:51 7,137 7,137 (G8 FORMULA = =H7-D8) (H8 FORMULA = =G8+(I8+J8+K8+L8+M8+N8+O8+P8+Q8+R8)
9
10
11 1-2-3-4 CG1 9-8-7-6 1511 1774 16.04.2018 01:35:23 =E11-D11 =G11+(I11+J11+K11+L11+M11+N11+O11+P11+Q11+R11)
12 1-2-3-4 CG1 9-8-7-6 301 1774 18.04.2018 11:17:23 =H12-D12 =G12+(I12+J12+K12+L12+M12+N12+O12+P12+Q12+R12)
13 1-2-3-4 CG1 9-8-7-6 166 1774 18.04.2018 12:49:50
14
15 1-2-3-5 CG1 9-8-7-5 667 1774 18.04.2018 22:11:12 =E15-D15 =G15+(I15+J15+K15+L15+M15+N15+O15+P15+Q15+R15)
16
17 1-2-3-6 CG1 9-8-7-4 62 1774 19.04.2018 12:41:36 =E17-D17 =G17+(I17+J17+K17+L17+M17+N17+O17+P17+Q17+R17)
18 1-2-3-6 CG1 9-8-7-4 40 1774 19.04.2018 14:02:03 =H18-D18 =G18+(I18+J18+K18+L18+M18+N18+O18+P18+Q18+R18)
19 1-2-3-6 CG1 9-8-7-4 1332 1774 23.04.2018 04:05:51
20 1-2-3-6 CG1 9-8-7-4 104 1774 23.04.2018 15:23:37
21 1-2-3-6 CG1 9-8-7-4 80 1774 23.04.2018 15:47:09
22
23 1-2-3-7 CG1 9-8-7-3 298 1774 23.04.2018 16:05:59
24
25 1-2-3-8 CG1 9-8-7-2 361 345 17.04.2018 12:44:57
26 1-2-3-9 CG1 9-8-7-3 478 345 18.04.2018 07:15:08
27 1-2-3-9 CG1 9-8-7-4 172 345 19.04.2018 10:54:35
28 1-2-3-9 CG1 9-8-7-5 14 536 14.04.2018 01:59:43
29 1-2-3-9 CG1 9-8-7-6 129 536 17.04.2018 16:19:23
30 1-2-3-9 CG1 9-8-7-7 294 536 23.04.2018 03:03:44

 

[Fluff]

Did the change fix the problem?

 

[Harley78]

Sorry, just tested it and yes, it works like a charm! Thank you so very much!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Harley78]

Anytime!

 

[Harley78]

Hello Fluff,

I am going through the code and wanting to learn what each line does and making notes. I do have a question. What line determines start cell for copying the formula. Example, i deleted rows that weren't necessary, leaving only the 4 cells with formulas for copy paste. When selecting copy formula it starts on line 14 and not 12 which does have line? Oh did add columns as you can see below. I did understand the lines up to why it isn't starting on line 12. Helpless I know

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	ECU	Component describtion	Planner	ICU	Product Short Description	REQ	Stock	Start time	INV	INV
2									after	plus
3									after	plus
4
5									build	rec
6
7									0	0
8									0	0
9
10
11
12	06-2109-7409-3-00	EBS ER100 NIS X60A MY14 ESC MEX PYA	CD2	06-2102-3466-4-97	HECU HW-EDM MK100 ESC PYA NISSAN X60A	128	668	23.04.2018 07:34:50
13
14	06-2109-7856-3-00	EBS ER100 MAZ J36W MY14 ESC	CD2	06-2102-4220-4-12	ICU Assy. Mazda J36W MK100 ESC Base 2015	10	141	23.04.2018 07:14:13	131	131
15
16	06-2109-7949-3-00	EBS ER100 MAZ J36A MY16 ESC 16CY	CD2	06-2102-4456-4-12	HECU MK100 ESC HW-EDM MAZ J36A MY16	740	3338	20.04.2018 06:03:01	2,598	2,598
17	06-2109-7949-3-00	EBS ER100 MAZ J36A MY16 ESC 16CY	CD2	06-2102-4456-4-12	HECU MK100 ESC HW-EDM MAZ J36A MY16	756	3338	25.04.2018 13:35:45	1,842	1,842
18
19	06-2109-7999-3-00	EBS ER100 MAZ J03A MY16 ESC IPM2 16CY	CD2	06-2102-4581-4-12	HW-EDM HYDR.ELECTR CONTR UNIT MK100 ESC	735	2297	19.04.2018 20:24:25	1,562	1,562
20
21	06-2109-8082-3-00	EBS ER100 MAZ J03W MY16 ESC	CD2	06-2102-4668-4-12	HW-EDM HECU MK100 ESC DSC Mazda J03W '16	556	138	20.04.2018 02:14:40	(418)	(418)
22	06-2109-8082-3-00	EBS ER100 MAZ J03W MY16 ESC	CD2	06-2102-4669-4-12	HW-EDM HECU MK100 ESC DSC Mazda J03W '16	551	138	20.04.2018 03:24:23	(969)	(969)
23
Demand Worksheet (2)

 

[Fluff]

Sub CopyFormula()
         Dim i As Long, j As Long
         Dim Ar As Areas
1        With Range("H7", Range("H" & Rows.count).End(xlUp)).SpecialCells(xlConstants)
2           Set Ar = .Areas
3           For i = 2 To Ar.count
4              Ar(i).Offset(, 1).Resize(1, 2).FormulaR1C1 = Range("I7:J7").FormulaR1C1
5              For j = 1 To Ar(i).count - 1
6                 Ar(i)(j).Offset(1, 1).Resize(1, 2).FormulaR1C1 = Range("I8:J8").FormulaR1C1
7              Next j
8           Next i
9        End With
End Sub

2) sets AR to each non contiguous area (range) in col H (in this case $H$12,$H$14,$H$16:$H$17,$H$19,$H$21:$H$22)
3) loops through the Areas starting at 2 (ie H14)
4) when i=3 then this line is effectively range("I16:J16").formulaR1C1=
5) loops through each range in the area
6) inserts the 2nd formula for each row in the area starting on the 2nd row

Originally you had data in H7:H8 which would have been the 1st area (ie AR(1)), but as there was the original formula in those rows, the code starts in AR(2).
Now you have removed that data you need the code to start with AR(1), so change

For i = 2 To Ar.count

this to i=1

 

[Harley78]

Again, many thanks and most of the lines I got and now confirmed. Thanks for the change and that helped and worked.