Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Copying Formulas to Multiple Rows

This is a discussion on Copying Formulas to Multiple Rows within the Excel Questions forums, part of the Question Forums category; ******** ******************** ************************************************************************> Microsoft Excel - Book2 ___Running: xl2002 XP : OS = Windows XP ( F )ile ( E ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    104

    Default 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
    =

    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
    0AD60*55177*1.35177930.6964921****ADAM6016*
    3
    1*AD690*3769*1.35254050.69524120.00138054.74586328.78933865.5ADAM6016ADAM690
    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
    0AD690*47913*******ADAM69016*
    9
    1*AD60*4295*******ADAM69016ADAM60
    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. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    104

    Default 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
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    IndexSite_IDNgbr_List#*of*NgbrsGridsComment*****Distance*Away*(mi)
    2
    0AD60*55177*1.35035350.6938663****
    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
    0AD690*47913*******
    9
    1*AD60*4295*******
    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. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default 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!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    104

    Default 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. #5
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459

    Default 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
    There are three kinds of people - those that can count and those that can't.

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330

    Default Re: Copying Formulas to Multiple Rows

    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?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    104

    Default 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. #8
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330

    Default Re: Copying Formulas to Multiple Rows

    Quote 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?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default 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
    =

    A
    B
    C
    D
    E
    F
    G
    H
    1
    IndexSite_IDNgbr_list#_Of_NgbrsGridsCommentsColumn_GColumn_H
    2
    0AD60*55177*formula_in_G2formula_in_H2
    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
    0AD690*47913*formula_in_G2formula_in_H2
    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?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Posts
    104

    Default 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

    Please help.

    Regards,

    Frank

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com