Excel: SELECT.END Macro Example Is Incorrect

SUMMARY

The macro example for SELECT.END on pages 212-213 in the "Microsoft Excel Function Reference" for version 3.0 does not work correctly. If the worksheet selection prior to running the macro contains cell(s) in Row 1 of the worksheet, a #NAME? error will result. Otherwise, the macro incorrectly includes the cell above the selection when creating the SUM formula.

MORE INFORMATION

Workaround

The errors are eliminated by adding 1 to the SelRows parameter as shown below:

  Change

     =FORMULA("=sum(R"&BotRow&"C:R"&BotRow-SelRows&"C)","R[2]C")

   to

     =FORMULA("=sum(R"&BotRow&"C:R"&BotRow-SelRows+1&"C)","R[2]C")

Steps to Reproduce Problem

1. Type the example macro on a new macro sheet including the names in column A. Highlight the information in both columns and choose Create Names from the Formula menu. Select Left Column and select OK. Then select the top cell in the macro (B4 in the book) and choose Define Name from the Formula menu. Enter any name in the Name text box, choose the Command option, and choose OK.

2. In a new worksheet, fill cells A1:A10 with the number 1.

3. Highlight A1:A10 on the worksheet and run the macro. The error #NAME? will appear in a cell two rows down from the selection.

   -or-

   Highlight A5:A10 on the worksheet and run the macro. The SUM formula created will be =SUM(A4:A10), which is incorrect.

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 212-213