Excel: Array Formula with GOTO or RETURN Doesn't Execute

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

SUMMARY

In Microsoft Excel, if a COMMAND+ENTERed array formula contains certain macro action-invoking functions, for example, GOTO or RETURN, the functions are not executed.

To correct this problem, break the formula into two separate statements. The array portion of the function should be COMMAND+ENTERed into one cell, with the portion containing the GOTO or RETURN entered into another cell. The result of the array formula can be passed as a parameter to the cell containing the GOTO or RETURN by referring to the reference of the COMMAND+ENTERed cell.

MORE INFORMATION

For example, when the following array formula is COMMAND+ENTERed, it returns the average of all the numbers in the range A1:A10 that are greater than 5: 

   =AVERAGE(IF(A1:A10>5,A1:A10))
To use this result in a conditional branching statement later in the macro, refer to this cell. For example, if you want to branch the operation to cell B1 if this average is greater than 20, use the following statement, where "ref" is the reference to the cell containing the AVERAGE formula: 

  =IF(ref>20,GOTO(B1))

KBCategory: kbother
KBSubcategory:

Additional reference 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.