Just to add:

The FormulaR1C1 property can be used with A1-style range formulas:

Sub Tester()
Range("B10").FormulaR1C1 = "=RC[-1]"
Range("K3").FormulaR1C1 = Range("B10").FormulaR1C1
Range("K4").Formula = Range("B10").Formula '<- not optimal
End Sub

Range("B10").FormulaR1C1 = "=RC[-1]"
-Puts a formula in B10 relative to itself.

Range("K3").FormulaR1C1 = Range("B10").FormulaR1C1
-Copies the formula from B10 to K3, maintaining relative integrity as if
it was copied and pasted from the clipboard.

Range("K4").Formula = Range("B10").Formula
-Copies the formula from B10 to K3 but does not adjust the references.
This is equivalent to copying the formula from the formula bar and then
pasting it into the destination. Probably not desired.

I think R1C1 notation takes some extra detective work to use. To me, this:

Range("A2").Formula = "=K2*5"
Range("X15:X25").FormulaR1C1 = Range("A1").FormulaR1C1

is easier to figure out than:

Range("A2").FormulaR1C1 = "=RC[9]*5"
'etc

IMO the strength of R1C1 style in VBA comes into play when formulas should
be relative to a range. Then if a column is inserted into the spreadsheet,
you'd have to adjust all of the A1-style formulas in VBA, which could be a
royal pain in the a**. Like Bob said, if formulas are using variables and
are relative, then that's probably the strongest case for R1C1 style.
However, if no variables are being inserted into formulas, why not just
store them on the spreadsheet and use the FormulaR1C1 property to write them
to wherever. This would protect the integrity if a column is inserted in the
destination sheet, because all that would be necessary would be to insert a
column in the formula sheet too to accommodate the adjustment.


Tim

"brym" wrote in message
...
Hi Bob! I see what you mean. But I rarely uses formulas like the ones
mentioned here.

Writing vba I find the new notation easier to work with, and thats where
I've had some problems interpretating macro-rec. code into my vba.
Probably
because I'm not familiar with it.

It seem to me as if the R1C1 is best in writing formulas but not writing
vba!

Anyhow, I'd love to read some examples, best if they compare the two
techniques. Know any places?