Results 1 to 8 of 8
Like Tree1Likes
  • 1 Post By fairwinds

FormulaR1C1

This is a discussion on FormulaR1C1 within the Excel Questions forums, part of the Question Forums category; Can someone explain simply what the properties of FormulaR1C1 are. Thanks V...

  1. #1
    Board Regular
    Join Date
    Sep 2003
    Location
    London
    Posts
    71

    Default FormulaR1C1

    Can someone explain simply what the properties of FormulaR1C1 are.

    Thanks

    V

  2. #2
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,634

    Default Re: FormulaR1C1

    From help file: (I think it is quite simle)

    In R1C1 reference style, Microsoft Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number. For example, the absolute cell reference R1C1 is equivalent to the absolute reference $A$1 in A1 reference style. If the active cell is A1, the relative cell reference R[1]C[1] refers to the cell one row down and one column to the right, or B2.

    The following are examples of references in R1C1 style.


    R[-2]C - A relative reference to the cell two rows up and in the same column
    R[2]C[2] - A relative reference to the cell two rows down and two columns to the right
    R2C2 - An absolute reference to the cell in the second row and in the second column
    R[-1] - A relative reference to the entire row above the active cell
    R - An absolute reference to the current row
    Data Bender likes this.
    "Fair Winds and Following Seas"

  3. #3
    Board Regular
    Join Date
    Sep 2003
    Location
    London
    Posts
    71

    Default Re: FormulaR1C1

    Thanks, thats just about simple enough

  4. #4
    New Member
    Join Date
    Apr 2011
    Posts
    11

    Default Re: FormulaR1C1

    So... if I wanted to fill in relative cell C3 with the word 'January', I would use...

    Range("B2") .Select
    ActiveCell.FormulaR1C1

    ...and Excel would move down one and across one from the active cell (in this case B2) and enter the word January?

    Obviously that's a convoluted way of doing it. But in terms of what the FormulaR1C1 does, is this correct?

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    71,377

    Default Re: FormulaR1C1

    The FormulaR1C1 property is like the Formula property, except that it requires an R1C1 style reference to be passed to it. It puts a formula in a range; it doesn't change the selection. What you need is one of:

    Code:
    ActiveCell.Offset(1, 1).Value = "January"
    ActiveCel.Cells(2, 2).Value = "January"
    ActiveCell.Range("B2").Value = "January"
    Microsoft MVP - Excel

  6. #6
    New Member
    Join Date
    Apr 2011
    Posts
    11

    Default Re: FormulaR1C1

    Ok, that makes more sense (I think). So the FormulaR1C1 bit is just specifying that it needs a cell reference type range for the 'January' text to be entered into? So I could technically remove the R1C1 part and as long as I kept a cell reference as the range then it would still work (although it could cause issues if a non-cell reference range was added)?

    I'm not trying to actually enter anything myself. Just trying to get a clear idea of FormulaR1C1.
    Last edited by Sid_London; Mar 25th, 2015 at 06:00 PM.

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    71,377

    Default Re: FormulaR1C1

    As I said the FormulaR1C1 property is like the Formula property, except that it requires an R1C1 style reference to be passed to it. It puts a formula in a range. If B2 contains the formula =C3, the equivalent in R1C1 reference style would be =R[1]C[1].
    Microsoft MVP - Excel

  8. #8
    New Member
    Join Date
    Apr 2011
    Posts
    11

    Default Re: FormulaR1C1

    Ok, thank you. I only started learning VBA two days ago, so apologies if my question seemed a bit naive.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com