Copying Formulas to Multiple Rows

Thanks:  0
Likes:  0

# Thread: Copying Formulas to Multiple Rows

1. ## Copying Formulas to Multiple Rows

******** ******************** ************************************************************************>
 Microsoft Excel - Book2 ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A1 =

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
1
IndexSite_IDNgbr_List# of NgbrsGridsComment Distance Away (mi)Site_ID# of NgbrsNgbr_List
2
3
4
2 CU04 1983 1.35699790.69897560.004707416.18288629.97070418.6ADAM6016CUMB04
5
3 YO35 1607 1.35095950.69793440.00157335.408640810.0168036.2ADAM6016YORK35
6
4 CU02 1483 1.36025630.69723620.006543122.4934741.65790725.9ADAM6016CUMB02
7
5 FK02 539 1.35528640.70067730.004972817.09509931.66012219.7ADAM6016FRAN02
8
9
10
2 CU04 3174 ADAM69016CUMB04
11
3 CU02 3010 ADAM69016CUMB02
12
4 YO35 2564 ADAM69016YORK35
13
0AL25 212589 ALLE2516
14
1 WS03 5637 ALLE2516WEST03
15
2 AL64 3839 ALLE2516ALLE64
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Columns G- L contain formulas that I want to paste down, but the blocks of data have varying row counts. I know I can just select the given number of rows and paste what I need, but I have so many rows to paste down to.

For Example:
B2 is a block of data with 6 rows associated to it. B8 has 5 rows of formulas. B13 has 3 rows of formulas.

I need to populate only the rows that are blank with formulas from the B2 thru Lxx.

I can copy manually, but I have quite a few rows and blocks. What I have been doing is copying and pasting one less row as I go down the list.

This may be a doozie!

Frank

2. ## Re: Copying Formulas to Multiple Rows

Here's the table with the formulas.

Thanks,

Frank

******** ******************** ************************************************************************>
 Microsoft Excel - Book2 ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G2H2G3H3I3J3K3L3G4H4I4J4K4L4G5H5I5J5K5L5G6H6I6J6K6L6G7H7I7J7K7L7 =

A
B
C
D
E
F
G
H
I
J
K
L
1
IndexSite_IDNgbr_List# of NgbrsGridsComment Distance Away (mi)
2
3
1 AD690 3769 1.34837320.69510591.02192473513.11846506.29534042.8
4
2 CU04 1983 1.35095950.69793441.02327843517.77216514.91394048.2
5
3 YO35 1607 1.34280690.69580011.02070673508.93126498.54054038.0
6
4 CU02 1483 1.34781060.69949311.02288093516.40546512.38284046.6
7
5 FK02 539 1.35254050.69524121.02300043516.81636513.14374047.1
8
9
10
2 CU04 3174
11
3 CU02 3010
12
4 YO35 2564
13
0AL25 212589
14
1 WS03 5637
15
2 AL64 3839
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

3. ## Re: Copying Formulas to Multiple Rows

Hi Frank:

Please clarify what is the formula that is going to be copied and to which cells. From your two posts, it appears you want to copy a formula from cell G1 to cells G2 through G7, G9 through G12, and G14 through G15 -- is it so?

A clearer explanation will help!

4. ## Re: Copying Formulas to Multiple Rows

Hi Yogi,

OK, Here goes.

I need the formula in G2, to be copied into G8 and G13, Etc. Also, copy the formula in H2 into H8 and H13, etc.

If you follow the pattern, G3 to G9 and G14 and H3 to H9 and H14 etc.

As you can see, there are a variety of rows to copy the formula to. One way I was thinking of doing it is using the Index Column (A) information.

For example:
If Ax contains "1" then copy this formula, if Ax contains "2" then copy this formula, if Ax contains "3" then copy this formula.

You might have an even better and more efficient way.

Does this make sense?

Thanks,

Frank

5. ## Re: Copying Formulas to Multiple Rows

If I read this right, you want to copy the block of formulas down.

How about something simple like:

Code:
```Sub Macro()
Range("G2:L7").Select
Selection.Copy
counter = 1
Do While counter < 5    'Change this for the number of times you want it to copy down
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
counter = counter + 1
Loop
Application.CutCopyMode = False
End Sub```

6. ## Re: Copying Formulas to Multiple Rows

Code:
`=VLOOKUP(INDIRECT(IF(G2=0,"M","O")&ROW()),'[PA Neighbor List FINAL TEMPLATE 03-09-04R2.xls]Calcs'!A:L,4,FALSE)`

7. ## Re: Copying Formulas to Multiple Rows

This works! Can I set a definitive number to it to automate it? Let's say whatever number is totaled in Row B500 for example.

Thanks,

Frank

8. ## Re: Copying Formulas to Multiple Rows

Originally Posted by fb250r
Can I set a definitive number to it to automate it? Let's say whatever number is totaled in Row B500 for example.

Thanks,

Frank
Can you clarify this?

9. ## Re: Copying Formulas to Multiple Rows

Hi Frank:

Good job on creating that that Index _Column. Well, let us have a look at the following ...

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G8H8G9H9G10H10G11H11G12H12G13H13G14H14G15H15 =

A
B
C
D
E
F
G
H
1
2
3
1 AD690 3769 formula_in_G3formula_in_H3
4
2 CU04 1983 formula_in_G4formula_in_H4
5
3 YO35 1607 formula_in_G5formula_in_H5
6
4 CU02 1483 formula_in_G6formula_in_H6
7
5 FK02 539 formula_in_G7formula_in_H7
8
9
1 AD60 4295 formula_in_G3formula_in_H3
10
2 CU04 3174 formula_in_G4formula_in_H4
11
3 CU02 3010 formula_in_G5formula_in_H5
12
4 YO35 2564 formula_in_G6formula_in_H6
13
0AL25 212589 formula_in_G2formula_in_H2
14
1 WS03 5637 formula_in_G3formula_in_H3
15
2 AL64 3839 formula_in_G4formula_in_H4
 Sheet6

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I have assumed you will have the starting formulas in cell G2 through G7, and H2 through H7, and so forth.

If I am right so far in my assumptions, then we need only one formula to copy the formulas to all other cells -- and the formula in cell G8 is ...

=INDEX(G\$2:G\$6,\$A8+1)

this formula is then copied as far down as necessary and acroos to columns H and beyond to the right as required.

Does it work for you?

10. ## Re: Copying Formulas to Multiple Rows

Hi Guys,

Yes, I think you all have the idea but none of the suggested solutions are working correctly.

This solution came the closest but it gets confused on different row counts.

Sub Macro()
Range("G2:L7").Select
Selection.Copy
counter = 1
Do While counter < 5 'Change this for the number of times you want it to copy down
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
counter = counter + 1
Loop
Application.CutCopyMode = False
End Sub

Regards,

Frank

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•