Excel: IF() Statement Returns FALSE if R1C1 Used in Logical

 Last reviewed: November 29, 1994
Article ID: Q25992
The information in this article applies to:
  • Microsoft Excel for the Macintosh, versions 1.x, 2.2, 3.0, 4.0

SUMMARY

In a Microsoft Excel macro, when you use a formula that contains an R1C1 reference in a logical argument, the formula always returns the value "FALSE".

For example, the following statement compares the value within the cell to the right of the active cell on the active worksheet with the value 2: 

   =IF("RC[1]"=2,RETURN())
The above statement should produce the result "TRUE" when the appropriate worksheet cell contains the value 2 and cause the RETURN() statement to stop the macro; however, it does not.

The first argument to the IF() function must always be a logical statement. In a logical expression, "RC[1]" is interpreted as text, not as a cell reference, as desired. This statement literally tests whether the set of characters "RC[1]" is equivalent to the number 2, which will always be false. A text string such as "RC[1]" will be interpreted as a reference only when it is used in a function that expects a reference argument.

Instead of using the above statement, use the following macro statement: 

   =IF(OFFSET(ACTIVE.CELL(),0,1)=2,RETURN())

KBCategory: kbusage
KBSubcategory:

Additional words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.2 2.20 3.0
3.00 4.0 4.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 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.