Microsoft KB Archive/45248

{| = Excel: Using R1C1 Notation When Using A1 Notation =
 * width="100%"|

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(&quot;R[1]C1&quot;) always returns FALSE because the text string &quot;R[1]C&quot; is not an empty text string.

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

=MID(&quot;R1C1&quot;,1,2) always returns &quot;R1&quot;, regardless of the text that may be contained in cell A1 because &quot;R1&quot; is the first two characters of the text string &quot;R1C1&quot;. These situations can be avoided by converting the text into references with the TEXTREF function. The statement TEXTREF(&quot;R[1]C1&quot;,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(&quot;R[1]C1&quot;,FALSE))

=IF(TEXTREF(&quot;R1C1&quot;,FALSE)=1,TRUE,FALSE)

=MID(TEXTREF(&quot;R1C1&quot;,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.
 * }