Macro to Show Named Ranges Containing the Active CellLast reviewed: September 2, 1997Article ID: Q68504 |
The information in this article applies to:
SUMMARYThe following Microsoft Excel macro finds all named ranges that contain the active cell on a worksheet and displays the names in a series of alert boxes. (Note: this is also true for version 5.0 macro sheets when you are using version 4.0 macros.)
MORE INFORMATIONCreate the following macro:
A1: find.names A2: =GET.DOCUMENT(1) A3: =FOR("count",1,COLUMNS(NAMES(A2))) A4: =INDEX(NAMES(A2),1,count) A5: =SUBSTITUTE(GET.NAME(A2&"!"&A4),"=","") A6: =IF(NOT(ISERROR(TEXTREF(A2&"!"&A4) ACTIVE.CELL())),ALERT("Cell is in : "&A4,2)) A7: =NEXT() A8: =ALERT("No more names",3) A9: =RETURN()Note: There is a space between "A4)" and "ACTIVE.CELL()" in cell A6. This space is the intersection operator. Running this macro will generate an alert box displaying a named range that contains the active cell, or a message saying there are "No more names." Choosing OK will continue to display named ranges containing the cell until no more ranges are left.
|
Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |