Copying Formulas to Multiple Rows

fb250r

Board Regular
Joined
Apr 25, 2002
Messages
104
Book2
ABCDEFGHIJKLMNO
1IndexSite_IDNgbr_List#ofNgbrsGridsCommentDistanceAway(mi)Site_ID#ofNgbrsNgbr_List
20AD60551771.35177930.6964921ADAM6016
31AD69037691.35254050.69524120.00138054.74586328.78933865.5ADAM6016ADAM690
42CU0419831.35699790.69897560.004707416.18288629.97070418.6ADAM6016CUMB04
53YO3516071.35095950.69793440.00157335.408640810.0168036.2ADAM6016YORK35
64CU0214831.36025630.69723620.006543122.4934741.65790725.9ADAM6016CUMB02
75FK025391.35528640.70067730.004972817.09509931.66012219.7ADAM6016FRAN02
80AD69047913ADAM69016
91AD604295ADAM69016ADAM60
102CU043174ADAM69016CUMB04
113CU023010ADAM69016CUMB02
124YO352564ADAM69016YORK35
130AL25212589ALLE2516
141WS035637ALLE2516WEST03
152AL643839ALLE2516ALLE64
Sheet1


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
(y)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Here's the table with the formulas.

Thanks,

Frank
Book2
ABCDEFGHIJKL
1IndexSite_IDNgbr_List#ofNgbrsGridsCommentDistanceAway(mi)
20AD60551771.35035350.6938663
31AD69037691.34837320.69510591.02192473513.11846506.29534042.8
42CU0419831.35095950.69793441.02327843517.77216514.91394048.2
53YO3516071.34280690.69580011.02070673508.93126498.54054038.0
64CU0214831.34781060.69949311.02288093516.40546512.38284046.6
75FK025391.35254050.69524121.02300043516.81636513.14374047.1
80AD69047913
91AD604295
102CU043174
113CU023010
124YO352564
130AL25212589
141WS035637
152AL643839
Sheet1
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
How about this formula in G2 and copy down?

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

Does this help you out?
 
Upvote 0
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

:pray:
 
Upvote 0
Hi Frank:

Good job on creating that that Index _Column. Well, let us have a look at the following ...
Book1
ABCDEFGH
1IndexSite_IDNgbr_list#_Of_NgbrsGridsCommentsColumn_GColumn_H
20AD6055177formula_in_G2formula_in_H2
31AD6903769formula_in_G3formula_in_H3
42CU041983formula_in_G4formula_in_H4
53YO351607formula_in_G5formula_in_H5
64CU021483formula_in_G6formula_in_H6
75FK02539formula_in_G7formula_in_H7
80AD69047913formula_in_G2formula_in_H2
91AD604295formula_in_G3formula_in_H3
102CU043174formula_in_G4formula_in_H4
113CU023010formula_in_G5formula_in_H5
124YO352564formula_in_G6formula_in_H6
130AL25212589formula_in_G2formula_in_H2
141WS035637formula_in_G3formula_in_H3
152AL643839formula_in_G4formula_in_H4
Sheet6


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?
 
Upvote 0
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

Please help.

Regards,

Frank
(y)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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