[rich8008]

How do you copy Excel Formulas using VBA so that they maintain there relative ranges. For example, I want to copy the Formula

=SUM(A1:A10) from cell A11 to cell D31 so that it is =SUM(D2130)

If I use something like Cell(4,31).Formula = Cell(1,11).Formula then I get the =SUM(A1:A10) in D31 which is not what I am trying to do.

To date I have always just summed it in VBA or something like that instead but I really can't avoid it anymore as the end user needs to be able to adjust values after the fact and not have to re-run the Macro.

I am struggling though creating the formulas using Address property of the new range but it is rather difficult and would require a re-write for almost any change.

Any help on this would be greatly appreciated. I have looked through my books, Help, internet and this forum and I can't seem to find anything.

[rich8008]

I used the following to copy the formula maintaining its relative reference. This seems pretty simple now, I don't know why I couldn't figure it out.

wks.Cells(i + 13, 7).FormulaR1C1 = wks.Cells(i, 3).FormulaR1C1