Help with copy past under blank row.

Harley78

Harley78

Active Member
Joined
Sep 27, 2007
Messages
372
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Sorry, I have figured out the Jeanie,,,,,, Hope this helps!



Excel Workbook
ABCDEFGH
1Part NumberPlannerICUREQStockStart timeINVINV
2afterplus
3afterplus
4
5buildrec
6
71-2-3-4CD29-8-7-6454876708.07.2013 19:18:028,3138,313
81-2-3-4CD29-8-7-6117609.07.2013 10:18:517,1377,137
9
10
11
121-2-3-4CG19-8-7-61511177416.04.2018 01:35:23
131-2-3-4CG19-8-7-6301177418.04.2018 11:17:23
141-2-3-4CG19-8-7-6166177418.04.2018 12:49:50
15
161-2-3-5CG19-8-7-5667177418.04.2018 22:11:12
17
181-2-3-6CG19-8-7-462177419.04.2018 12:41:36
191-2-3-6CG19-8-7-440177419.04.2018 14:02:03
201-2-3-6CG19-8-7-41332177423.04.2018 04:05:51
211-2-3-6CG19-8-7-4104177423.04.2018 15:23:37
221-2-3-6CG19-8-7-480177423.04.2018 15:47:09
23
241-2-3-7CG19-8-7-3298177423.04.2018 16:05:59
25
261-2-3-8CG19-8-7-236134517.04.2018 12:44:57
271-2-3-9CG19-8-7-347834518.04.2018 07:15:08
281-2-3-9CG19-8-7-417234519.04.2018 10:54:35
291-2-3-9CG19-8-7-51453614.04.2018 01:59:43
301-2-3-9CG19-8-7-612953617.04.2018 16:19:23
311-2-3-9CG19-8-7-729453623.04.2018 03:03:44
321-2-3-9CG19-8-7-8112694418.04.2018 09:59:28
331-2-3-9CG19-8-7-972094418.04.2018 20:59:59
341-2-3-9CG19-8-7-1074094420.04.2018 01:06:21
351-2-3-9CG19-8-7-11618315816.04.2018 03:01:18
361-2-3-9CG19-8-7-12527315816.04.2018 04:24:45
TEST
 
Last edited:
Upvote 0
is this what you want
Code: 
Sub CopyFormula()
   Dim i As Long
   Dim Ar As Areas
   With Range("F7", Range("F" & Rows.count).End(xlUp)).SpecialCells(xlConstants)
      Set Ar = .Areas
      For i = 2 To Ar.count
         Ar(i).Offset(, 1).Resize(1, 2).FormulaR1C1 = Range("G7:H7").FormulaR1C1
         If Ar(i).count > 1 Then Ar(i).Offset(1, 1).Resize(1, 2).FormulaR1C1 = Range("G8:H8").FormulaR1C1
      Next i
   End With
End Sub
 
Upvote 0
Awesome!!!!! that is exactly how I am trying to do, Thanks so much..... now onto double clicking the last cell for those that have two or more lines.

Again, thanks so much
 
Upvote 0
If you want to copy the 2nd formula down to the bottom of each section try
Code: 
Sub CopyFormula()
   Dim i As Long
   Dim Ar As Areas
   With Range("F7", Range("F" & Rows.count).End(xlUp)).SpecialCells(xlConstants)
      Set Ar = .Areas
      For i = 2 To Ar.count
         Ar(i).Offset(, 1).Resize(1, 2).FormulaR1C1 = Range("G7:H7").FormulaR1C1
         If Ar(i).count > 1 Then Ar(i).Offset(1, 1).Resize([COLOR=#0000ff]Ar(i).count - 1[/COLOR], 2).FormulaR1C1 = Range("G8:H8").FormulaR1C1
      Next i
   End With
End Sub
 
Upvote 0
That second one didn't seem to work, when i add quantities to a cell, it doesn't seem to change the group figures.

First code produced this and is exactly what I wanted it to do.
Excel Workbook
ABCDEFGH
1BlocksPlannerICUREQStockStart timeINVINV
2afterplus
3afterplus
4
5buildrec
6
710-0961-0153-3-00CD210-0212-0919-4-11454876708.07.2013 19:18:028,3138,323
810-0961-0153-3-00CD210-0212-0919-4-11117609.07.2013 10:18:517,1477,147
9
10
11
1206-2104-0207-1-00CG106-2102-5726-4-971511177416.04.2018 01:35:23263263
1306-2104-0207-1-00CG106-2102-4866-4-97301177418.04.2018 11:17:23(38)(38)
1406-2104-0207-1-00CG106-2102-4902-4-97166177418.04.2018 12:49:50
1506-2104-0207-1-00CG106-2102-4866-4-97667177418.04.2018 22:11:12
1606-2104-0207-1-00CG106-2102-5024-4-9762177419.04.2018 12:41:36
1706-2104-0207-1-00CG106-2102-4408-4-9740177419.04.2018 14:02:03
1806-2104-0207-1-00CG106-2102-4866-4-971332177423.04.2018 04:05:51
1906-2104-0207-1-00CG106-2102-4902-4-97104177423.04.2018 15:23:37
2006-2104-0207-1-00CG106-2102-5024-4-9780177423.04.2018 15:47:09
2106-2104-0207-1-00CG106-2102-4902-4-97298177423.04.2018 16:05:59
22
2306-2104-0220-1-00CG106-2102-4956-4-9436134517.04.2018 12:44:57(16)(16)
2406-2104-0220-1-00CG106-2102-4956-4-9447834518.04.2018 07:15:08(494)(494)
2506-2104-0220-1-00CG106-2102-4956-4-9417234519.04.2018 10:54:35
26
2706-2104-0224-1-00CG106-2102-3466-4-971453614.04.2018 01:59:43522522
2806-2104-0224-1-00CG106-2102-4816-4-9712953617.04.2018 16:19:23393393
2906-2104-0224-1-00CG106-2102-3466-4-9729453623.04.2018 03:03:44
30
3106-2104-0225-1-00CG106-2102-5381-4-12112694418.04.2018 09:59:28(182)(182)
3206-2104-0225-1-00CG106-2102-5380-4-1272094418.04.2018 20:59:59(902)(902)
3306-2104-0225-1-00CG106-2102-4456-4-1274094420.04.2018 01:06:21
34
3506-2104-0228-1-00CG106-2102-4864-4-97618315816.04.2018 03:01:182,5402,540
3606-2104-0228-1-00CG106-2102-4901-4-97527315816.04.2018 04:24:452,0132,013
3706-2104-0228-1-00CG106-2102-4864-4-9790315816.04.2018 13:13:19
3806-2104-0228-1-00CG106-2102-4864-4-97329315817.04.2018 14:00:12
3906-2104-0228-1-00CG106-2102-4901-4-97326315817.04.2018 14:42:41
4006-2104-0228-1-00CG106-2102-5725-4-971661315817.04.2018 15:49:24
4106-2104-0228-1-00CG106-2102-4864-4-97292315818.04.2018 01:29:55
4206-2104-0228-1-00CG106-2102-4864-4-97292315819.04.2018 17:53:57
4306-2104-0228-1-00CG106-2102-4901-4-97265315819.04.2018 18:31:59
4406-2104-0228-1-00CG106-2102-4864-4-97607315823.04.2018 07:05:49
4506-2104-0228-1-00CG106-2102-4901-4-97528315823.04.2018 18:35:13
46
4706-2104-0230-1-00CG106-2102-4804-4-12754142620.04.2018 04:32:02672672
4806-2104-0230-1-00CG106-2102-4803-4-12171142620.04.2018 08:24:20501501
49
5006-2104-0233-1-00CG106-2102-4668-4-125568420.04.2018 06:05:30(472)(472)
5106-2104-0233-1-00CG106-2102-4669-4-125518420.04.2018 07:15:13(1,023)(1,023)
52
5306-2104-0237-1-00CG106-2102-5949-4-9714914523.04.2018 05:15:14(4)(4)
54
5506-2104-0240-1-00CG106-2102-4955-4-9418192915.04.2018 07:35:071,9111,911
5606-2104-0240-1-00CG106-2102-4955-4-94751192916.04.2018 09:09:581,1601,160
5706-2104-0240-1-00CG106-2102-4782-4-94560192916.04.2018 10:43:05
5806-2104-0240-1-00CG106-2102-4955-4-94967192917.04.2018 11:57:10
5906-2104-0240-1-00CG106-2102-4782-4-94560192917.04.2018 22:29:05
6006-2104-0240-1-00CG106-2102-4955-4-94512192917.04.2018 23:39:17
6106-2104-0240-1-00CG106-2102-4955-4-94948192919.04.2018 15:50:12
TEST 2


Now when I select G13 and H13 and double click it should look like this.
Excel Workbook
ABCDEFGH
1BlocksPlannerICUREQStockStart timeINVINV
2afterplus
3afterplus
4
5buildrec
6
710-0961-0153-3-00CD210-0212-0919-4-11454876708.07.2013 19:18:028,3138,323
810-0961-0153-3-00CD210-0212-0919-4-11117609.07.2013 10:18:517,1477,147
9
10
11
1206-2104-0207-1-00CG106-2102-5726-4-971511177416.04.2018 01:35:23263263
1306-2104-0207-1-00CG106-2102-4866-4-97301177418.04.2018 11:17:23(38)(38)
1406-2104-0207-1-00CG106-2102-4902-4-97166177418.04.2018 12:49:50(204)(204)
1506-2104-0207-1-00CG106-2102-4866-4-97667177418.04.2018 22:11:12(871)(871)
1606-2104-0207-1-00CG106-2102-5024-4-9762177419.04.2018 12:41:36(933)(933)
1706-2104-0207-1-00CG106-2102-4408-4-9740177419.04.2018 14:02:03(973)(973)
1806-2104-0207-1-00CG106-2102-4866-4-971332177423.04.2018 04:05:51(2,305)(2,305)
1906-2104-0207-1-00CG106-2102-4902-4-97104177423.04.2018 15:23:37(2,409)(2,409)
2006-2104-0207-1-00CG106-2102-5024-4-9780177423.04.2018 15:47:09(2,489)(2,489)
2106-2104-0207-1-00CG106-2102-4902-4-97298177423.04.2018 16:05:59(2,787)(2,787)
TEST 2



The new vba does this.

Excel Workbook
ABCDEFGH
1BlocksPlannerICUREQStockStart timeINVINV
2afterplus
3afterplus
4
5buildrec
6
710-0961-0153-3-00CD210-0212-0919-4-11454876708.07.2013 19:18:028,3138,313
810-0961-0153-3-00CD210-0212-0919-4-11117609.07.2013 10:18:517,1377,137
9
10
11
1206-2104-0207-1-00CG106-2102-5726-4-971511177416.04.2018 01:35:23263263
1306-2104-0207-1-00CG106-2102-4866-4-97301177418.04.2018 11:17:23(38)(38)
1406-2104-0207-1-00CG106-2102-4902-4-97166177418.04.2018 12:49:50(1,373)(706)
1506-2104-0207-1-00CG106-2102-4866-4-97667177418.04.2018 22:11:12(706)224
1606-2104-0207-1-00CG106-2102-5024-4-9762177419.04.2018 12:41:36(120)(666)
1706-2104-0207-1-00CG106-2102-4408-4-9740177419.04.2018 14:02:0322499
1806-2104-0207-1-00CG106-2102-4866-4-971332177423.04.2018 04:05:51(361)(16)
1906-2104-0207-1-00CG106-2102-4902-4-97104177423.04.2018 15:23:37(666)0
2006-2104-0207-1-00CG106-2102-5024-4-9780177423.04.2018 15:47:09(14)522
2106-2104-0207-1-00CG106-2102-4902-4-97298177423.04.2018 16:05:59990
22
TEST 3
 
Upvote 0
Ok, try this
Code: 
Sub CopyFormula()
   Dim i As Long, j As Long
   Dim Ar As Areas
   With Range("F7", Range("F" & Rows.count).End(xlUp)).SpecialCells(xlConstants)
      Set Ar = .Areas
      For i = 2 To Ar.count
         Ar(i).Offset(, 1).Resize(1, 2).FormulaR1C1 = Range("G7:H7").FormulaR1C1
         For j = 2 To Ar(i).count - 1
            Ar(i)(j).Offset(1, 1).Resize(1, 2).FormulaR1C1 = Range("G8:H8").FormulaR1C1
         Next j
      Next i
   End With
End Sub
 
Upvote 0
I think it is close however blank cells are showing up. ie 13g 13 h... 32g 32 H However, when i manually copy and past the G8 H8 into the blank cells, it is correct.

Excel Workbook
ABCDEFGH
1BlocksPlannerICUREQStockStart timeINVINV
2afterplus
3afterplus
4
5buildrec
6
710-0961-0153-3-00CD210-0212-0919-4-11454876708.07.2013 19:18:028,3138,313
810-0961-0153-3-00CD210-0212-0919-4-11117609.07.2013 10:18:517,1377,137
9
10
11
1206-2104-0207-1-00CG106-2102-5726-4-971511177416.04.2018 01:35:23263263
1306-2104-0207-1-00CG106-2102-4866-4-97301177418.04.2018 11:17:23
1406-2104-0207-1-00CG106-2102-4902-4-97166177418.04.2018 12:49:50(166)(166)
1506-2104-0207-1-00CG106-2102-4866-4-97667177418.04.2018 22:11:12(833)(833)
1606-2104-0207-1-00CG106-2102-5024-4-9762177419.04.2018 12:41:36(895)(895)
1706-2104-0207-1-00CG106-2102-4408-4-9740177419.04.2018 14:02:03(935)(935)
1806-2104-0207-1-00CG106-2102-4866-4-971332177423.04.2018 04:05:51(2,267)(2,267)
1906-2104-0207-1-00CG106-2102-4902-4-97104177423.04.2018 15:23:37(2,371)(2,371)
2006-2104-0207-1-00CG106-2102-5024-4-9780177423.04.2018 15:47:09(2,451)(2,451)
2106-2104-0207-1-00CG106-2102-4902-4-97298177423.04.2018 16:05:59(2,749)(2,749)
22
2306-2104-0220-1-00CG106-2102-4956-4-9436134517.04.2018 12:44:57(16)(16)
2406-2104-0220-1-00CG106-2102-4956-4-9447834518.04.2018 07:15:08
2506-2104-0220-1-00CG106-2102-4956-4-9417234519.04.2018 10:54:35(172)(172)
26
2706-2104-0224-1-00CG106-2102-3466-4-971453614.04.2018 01:59:43522522
2806-2104-0224-1-00CG106-2102-4816-4-9712953617.04.2018 16:19:23
2906-2104-0224-1-00CG106-2102-3466-4-9729453623.04.2018 03:03:44(294)(294)
30
3106-2104-0225-1-00CG106-2102-5381-4-12112694418.04.2018 09:59:28(182)(182)
3206-2104-0225-1-00CG106-2102-5380-4-1272094418.04.2018 20:59:59
3306-2104-0225-1-00CG106-2102-4456-4-1274094420.04.2018 01:06:21(740)(740)
34
3506-2104-0228-1-00CG106-2102-4864-4-97618315816.04.2018 03:01:182,5402,540
3606-2104-0228-1-00CG106-2102-4901-4-97527315816.04.2018 04:24:45
3706-2104-0228-1-00CG106-2102-4864-4-9790315816.04.2018 13:13:19(90)(90)
3806-2104-0228-1-00CG106-2102-4864-4-97329315817.04.2018 14:00:12(419)(419)
3906-2104-0228-1-00CG106-2102-4901-4-97326315817.04.2018 14:42:41(745)(745)
4006-2104-0228-1-00CG106-2102-5725-4-971661315817.04.2018 15:49:24(2,406)(2,406)
4106-2104-0228-1-00CG106-2102-4864-4-97292315818.04.2018 01:29:55(2,698)(2,698)
4206-2104-0228-1-00CG106-2102-4864-4-97292315819.04.2018 17:53:57(2,990)(2,990)
4306-2104-0228-1-00CG106-2102-4901-4-97265315819.04.2018 18:31:59(3,255)(3,255)
4406-2104-0228-1-00CG106-2102-4864-4-97607315823.04.2018 07:05:49(3,862)(3,862)
4506-2104-0228-1-00CG106-2102-4901-4-97528315823.04.2018 18:35:13(4,390)(4,390)
46
4706-2104-0230-1-00CG106-2102-4804-4-12754142620.04.2018 04:32:02672672
4806-2104-0230-1-00CG106-2102-4803-4-12171142620.04.2018 08:24:20
49
5006-2104-0233-1-00CG106-2102-4668-4-125568420.04.2018 06:05:30(472)(472)
5106-2104-0233-1-00CG106-2102-4669-4-125518420.04.2018 07:15:13
52
5306-2104-0237-1-00CG106-2102-5949-4-9714914523.04.2018 05:15:14(4)(4)
54
TEST 3
 
Last edited:
Upvote 0
Apologies, this
Code: 
         For j = 2 To Ar(i).count - 1
Should be j=1 not j=2
 
Upvote 0
No apologies needed. your helping me tremendously! I appreciate it very much. i wish i had 10% of what you have when doing this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
right , left , search, find
Replies
2
Views
266
RoryA
RoryA
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
59
Skyybot
Skyybot
E
  • Solved
Using Excel to copy and paste one of 9 different replies
Replies
6
Views
274
ellison
E
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
128
Fluff
Fluff
F
  • Solved
Formula to find location of second row from the bottom containing specific text
Replies
5
Views
104
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,798
Messages
6,181,038
Members
453,014
Latest member
Chris258

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top