Cells(cRow, 10).FormulaR1C1 = Cells(cRow - 1, 10).FormulaR1C1

is one way to do it.

As to which is the "best" way, it depends. PasteSpecial is very useful for a
lot of formulas. For one offs, I prefer the above.


"Ed Bitzer" wrote in message
...
To copy a formula (with smart adjustment for the new location) from cRow
4 to cRow 5 where cRow = 5 I can use:
ActiveSheet.Cells(cRow - 1, 10).Copy
ActiveSheet.Cells(cRow, 10).PasteSpecial Paste:=xlPasteFormulas
Is this the "best" way?
I am surprised that this:
Cells(cRow, 10).Formula = Cells(cRow - 1, 10).Formula
does not work. It copies the formula but without adjustment for the new
location.

Ed

That's clever.

--
Regards,
Tom Ogilvy

Tim Zych wrote in message
...
Cells(cRow, 10).FormulaR1C1 = Cells(cRow - 1, 10).FormulaR1C1

is one way to do it.

As to which is the "best" way, it depends. PasteSpecial is very useful for
a
lot of formulas. For one offs, I prefer the above.

Thanks. The original inspiration for this approach was provided by John
Green and Vasant Nanavati during a discussion a while back. Just to add,
FormulaR1C1 is the only (non paste special) way I know of that can properly
assign noncontiguous formulas in one action:

Range("A6, A10, A12").FormulaR1C1 = Range("A3").FormulaR1C1

Tim

"Tom Ogilvy" wrote in message
...
That's clever.

--
Regards,
Tom Ogilvy

Tim Zych wrote in message
...
Cells(cRow, 10).FormulaR1C1 = Cells(cRow - 1, 10).FormulaR1C1

is one way to do it.

As to which is the "best" way, it depends. PasteSpecial is very useful
for
a
lot of formulas. For one offs, I prefer the above.