Excel: Using R1C1 Notation When Using A1 Notation

Last reviewed: November 2, 1994
Article ID: Q45248

SUMMARY

When testing cell contents from a macro in Microsoft Excel, R1C1 style text references must be converted from text if the environment is set to A1 notation (that is, R1C1 is not selected in Options Workspace). In most cases, Excel treats R1C1 references as pure text if the environment is set to A1 notation, resulting in erroneous or misleading comparisons.

MORE INFORMATION

Examples of these erroneous or misleading comparisons are shown in the following examples:

   =ISBLANK("R[1]C1") always returns FALSE because the text string
   "R[1]C" is not an empty text string.

   =IF("R1C1"=1,TRUE,FALSE) always returns FALSE, even if A1
   contains the number "1" because the number 1 is not equal to the
   text string "R1C1".

   =MID("R1C1",1,2) always returns "R1", regardless of the text that
   may be contained in cell A1 because "R1" is the first two characters
   of the text string "R1C1".

These situations can be avoided by converting the text into references with the TEXTREF function. The statement TEXTREF("R[1]C1",FALSE) returns the reference R[1]C1, which can then be used to find the contents of that cell. As a result, the correct syntax for the above examples becomes the following:

   =ISBLANK(TEXTREF("R[1]C1",FALSE))

   =IF(TEXTREF("R1C1",FALSE)=1,TRUE,FALSE)

   =MID(TEXTREF("R1C1",FALSE),1,2)

Note that there are a few functions, such as the SELECT statement, that do correctly treat text-style R1C1 references as references. These functions do not accept text arguments, so there is no ambiguity as to whether you intend them to be used as text or as a reference.

KBCategory: kbother
KBSubcategory:
Additional reference words: 1.50 2.20 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.