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

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())

Additional query words:

Keywords :
Version :
Platform :
Issue type :