Firstly, Formula and FormulaR1C1 should only be used when creating a
formula, it is not necessary just to set a value, Value is good enough for
that.

Formula is best used when you want to set up a relatively straight-forward
formula using A1 notation, such as =SUM(A1:A10), or
=VLOOKUP(A1,M1:O20,2,False), and you know that the referenced cells are
locked in position, that is row or column insertion and deletion does not
move the formula references. If this happens, it is much harder to set the
formula using A1 notation trying to determine the column letter relative to
a know point (such as the cell containing the formula).

If the formula could be added at various points, and you want to reference a
point relative to a known point, I think R1C1 notation is simpler. For
instance, creating a formula of adding 3 cells to the right is simply done
with activecell.FormulaR1C1 = "=SUM(RC[+1]:RC[+3])", which is a tad more
difficult with A1 notation. Using variables, for say the row number is also
easy in this style, activecell.FormulaR1C1 = "=SUM(R" & iRow & "C[+1]:R" &
iRow + 2 & "C[+3])" where iRow contains a start row number.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joe" wrote in message
...
Hi all
Please point when to use each (using VBA...)
TIA
Guy