Microsoft KB Archive/68504

{|
 * width="100%"|

Macro to Show Named Ranges Containing the Active Cell

 * }

Q68504

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.0, 2.01, 2.1, 2.10c, 2.10d, 3.0, 4.0, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 3.0

-

SUMMARY
The 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 INFORMATION
Create the following macro:

A1:    find.names A2:    =GET.DOCUMENT(1) A3:    =FOR(&quot;count&quot;,1,COLUMNS(NAMES(A2))) A4:    =INDEX(NAMES(A2),1,count) A5:    =SUBSTITUTE(GET.NAME(A2&&quot;!&quot;&A4),&quot;=&quot;,&quot;&quot;) A6:    =IF(NOT(ISERROR(TEXTREF(A2&&quot;!&quot;&A4) ACTIVE.CELL)),ALERT(&quot;Cell is in : &quot;&A4,2)) A7:    =NEXT A8:    =ALERT(&quot;No more names&quot;,3) A9:    =RETURN Note: There is a space between &quot;A4)&quot; and &quot;ACTIVE.CELL&quot; 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 &quot;No more names.&quot; 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 4.0 5.0

Keywords : kbcode kbmacro kbprogramming

Issue type :

Technology : kbExcelSearch kbExcel500 kbExcelOS2Search kbExcel400 kbExcel220OS2 kbExcel300OS2 kbExcel201 kbExcel210c kbExcel210d kbExcel210 kbExcel300 kbExcel200