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

Thread: Copying Formulas to Multiple Rows

  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

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