+ Reply to Thread
Results 1 to 4 of 4

Formula VS Formula R1C1

  1. #1
    Joe
    Guest

    Formula VS Formula R1C1

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



  2. #2
    Niek Otten
    Guest

    Re: Formula VS Formula R1C1

    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" <[email protected]> wrote in message
    news:[email protected]...
    > Hi all
    > Please point when to use each (using VBA...)
    > TIA
    > Guy
    >




  3. #3
    Bob Phillips
    Guest

    Re: Formula VS Formula R1C1

    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" <[email protected]> wrote in message
    news:[email protected]...
    > Hi all
    > Please point when to use each (using VBA...)
    > TIA
    > Guy
    >
    >




  4. #4
    Mike Fogleman
    Guest

    Re: Formula VS Formula R1C1

    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" <[email protected]> wrote in message
    news:[email protected]...
    > Hi all
    > Please point when to use each (using VBA...)
    > TIA
    > Guy
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1