Formula VS Formula R1C1

Hi all
Please point when to use each (using VBA...)
TIA
Guy

Most of the time you would use Formula.
But if for example you write a UDF that displays the formula of a cell, you
might want to display it in the adressing style that is used in the
workbook. So then you test to see what adressing style was used and choose
between Formula and FormulaR1C1. Possibly also between Formula and
FormulaLocal (and the R1C1 option of Local).
Same when you use text from the worksheet to construct a formula and write
that to a cell.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Joe" <support@kid-chess.co.il> wrote in message
news:uMDIJuRhFHA.3164@TK2MSFTNGP15.phx.gbl...
> Hi all
> Please point when to use each (using VBA...)
> TIA
> Guy
>

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" <support@kid-chess.co.il> wrote in message
news:uMDIJuRhFHA.3164@TK2MSFTNGP15.phx.gbl...
> Hi all
> Please point when to use each (using VBA...)
> TIA
> Guy
>
>

Bob wrote:
>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).

A cool workaround for this is using a named range which Excel keeps track of
wherever it may have moved to on the worksheet. For example you select A1:C3
(a 9 cell block) and give it a Name in the menu Insert/Name/Define something
like "Formulas_A". That 9-cell block now becomes a miniature spreadsheet.
Just like the regular worksheet, you can now refer to cells within the named
range in A1 notation. The upper left cell will always be A1 and the lower
right cell will always be C3, within that range, and wherever that range is
on the worsheet, as long as that range is kept intact with the original 9
cells and not broken up.
In VBA you would use the .Range.Range method to address the cells within the
named range in A1 notation. For example:

Sheets("Sheet1").Range("Formulas_A").Range("A1:C3").Value = "0"

That would make all 9 cells display "0". If the named range "Formulas_A" had
been moved so the upper left corner is on H7, then H7:J9 will be 0's.
An example of R1C1 would be:

Sheets("Sheet1").Range("Formulas_A")(3, 3).Value = "0"

This would only make J9 =0

The following would put a formula in H7:

Range("Formulas_A").Range("A1").Formula = "=IF(AND(A2=A3,B3=""Y""),1,0)"

Is this making sense to you?
Mike F



"Joe" <support@kid-chess.co.il> wrote in message
news:uMDIJuRhFHA.3164@TK2MSFTNGP15.phx.gbl...
> Hi all
> Please point when to use each (using VBA...)
> TIA
> Guy
>