Modify Macro to copy formula to new range and update cell references

    Hi!


    I need a macro that copies formulae from multiple cells in a row to multiple rows and also updates the cell references.


    For example, if cell A10 has formula =AA10+AB10, cell B10 has formula AB10+AC10, I need to copy these formula upwards to rows A2:B9 (each formula is to be copied upwards in the same column), so that formula in A9 is =AA9+AB9, in B9 is AB9+AC9, in A8 is AA8+AB8, in B8 is AB8+AC8 and so on as below:


    A2...
    ...
    A8=AA8+AB8; B8=AB8+AC8 (New copied formula)
    A9=AA9+AB9; B9=AB9+AC9


    A10=AA10+AB10; B10=AB10+AC10 (original formula)


    The following macro copies formula in a range to a new range, but the starting reference is the same as in the original formula. For example, if the formula in cell A10 is AA10+AB10, the formula in Cell A2 will be AA10+AB10 (same as in cell A10) and is copied down so that formula in Cell A3 is AA11+AB11 instead of AA3+AB3


    Code
    Sub formula ()
With Sheets ("Sheet1")
 .Range("A2:D9").formula = .Range("A10:D10").formula
End With
End Sub


    Is it possible to modify it to update cell references after copying to new range

    Re: Modify Macro to copy formula to new range and update cell references


    Try this:


    Code
    Sub formula () 
    With Sheets ("Sheet1") 
        .Range("A2:D9").FormulaR1C1 = .Range("A10:D10").FormulaR1C1
    End With 
End Sub


    note: not sure if it's a typo but the code above doesn't follow the conventions of your post - e.g. should A2:D9 not be A2:D2 or A9:D9?

    Re: Modify Macro to copy formula to new range and update cell references


    Thanks for the reply S O,


    The code almost works. In the sense that the first row where the code is copied now takes A2=AA2+AB2 BUT the increment in next rows is in Twos..


    So that when I run the above code, A2=AA2+AB2 (which is correct and as required) ... but A3 is now =AA4+AB4, A4 is now =AA6+AB6


    Am attaching a sample workbook with the data and code in it.
    Can you please take a look and advise.


    PS: My request was not a typo. I want to copy the formula in Row 10 (A10:D:10) to rows 2 to 9 i.e. to the range A2:D:9 just as would happen when one would select cells A10:D10 and drag them up or down (your code understands my requirement exactly).

    Re: Modify Macro to copy formula to new range and update cell references


    Bump!


    Can someone please review the problem in the solution given by S O and advise how to rectify the issue being faced by me?


    Thanx

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login