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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
right , left , search, find
Replies
2
Views
168
RoryA
RoryA
N
  • Question
Need Formula help
Replies
5
Views
358
AhoyNC
A
G
  • Solved
Help on Xlookup
Replies
5
Views
153
Alex Blakenburg
A
M
  • Solved
Find 6 digit PO using Xlookup in a string of POs in a cell.
Replies
5
Views
165
Peter_SSs
Peter_SSs
A
  • Question
Hi everyone. Help wit Excel!
Replies
2
Views
114
StephenCrump
S

Forum statistics

Threads
1,214,940
Messages
6,122,352
Members
449,080
Latest member
Armadillos

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