Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use VBA to find which named ranges a cell belongs to?
I'm tring to find code which allows me to verify which named ranges any
single cell belongs to |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use VBA to find which named ranges a cell belongs to?
Give this a try...
Sub test() Dim nme As Name Dim rng As Range Set rng = Range("D4") For Each nme In ThisWorkbook.Names If Not Intersect(rng, nme.RefersToRange) Is Nothing Then MsgBox nme.Name Next nme End Sub -- HTH... Jim Thomlinson "Charis Custard" wrote: I'm tring to find code which allows me to verify which named ranges any single cell belongs to |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use VBA to find which named ranges a cell belongs to?
To the OP
To avoid 1004 errors, you might add code check that the two ranges are on the same worksheet before try to intersect them. For example Intersect(worksheets(1).Rows(1),worksheets(2).colu mns(1)).Address raises a 1004 error. Another approach is to error trap. Here is some code I posted back in October 1999 (slightly modified) that demonstrates that approach: Sub TestName() Dim rng As Range definedName as Name On Error Resume Next For Each definedName In ThisWorkbook.Names Set rng = Nothing Set rng = definedName.RefersToRange If Not rng Is Nothing Then If Not Intersect(rng, ActiveCell) Is Nothing Then MsgBox ActiveCell.Address & " is in a range named: " & definedName.Name End If End If Next End Sub This has the advantage that it accounts for defined names that are not ranges as well. (a defined name does not have to refer to a range). -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: Give this a try... Sub test() Dim nme As Name Dim rng As Range Set rng = Range("D4") For Each nme In ThisWorkbook.Names If Not Intersect(rng, nme.RefersToRange) Is Nothing Then MsgBox nme.Name Next nme End Sub -- HTH... Jim Thomlinson "Charis Custard" wrote: I'm tring to find code which allows me to verify which named ranges any single cell belongs to |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use VBA to find which named ranges a cell belongs to?
I think you need to add Dim (or the line wrapped)...
Dim definedName As Name Otherwise it is better code in all respects. -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: To the OP To avoid 1004 errors, you might add code check that the two ranges are on the same worksheet before try to intersect them. For example Intersect(worksheets(1).Rows(1),worksheets(2).colu mns(1)).Address raises a 1004 error. Another approach is to error trap. Here is some code I posted back in October 1999 (slightly modified) that demonstrates that approach: Sub TestName() Dim rng As Range definedName as Name On Error Resume Next For Each definedName In ThisWorkbook.Names Set rng = Nothing Set rng = definedName.RefersToRange If Not rng Is Nothing Then If Not Intersect(rng, ActiveCell) Is Nothing Then MsgBox ActiveCell.Address & " is in a range named: " & definedName.Name End If End If Next End Sub This has the advantage that it accounts for defined names that are not ranges as well. (a defined name does not have to refer to a range). -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: Give this a try... Sub test() Dim nme As Name Dim rng As Range Set rng = Range("D4") For Each nme In ThisWorkbook.Names If Not Intersect(rng, nme.RefersToRange) Is Nothing Then MsgBox nme.Name Next nme End Sub -- HTH... Jim Thomlinson "Charis Custard" wrote: I'm tring to find code which allows me to verify which named ranges any single cell belongs to |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use VBA to find which named ranges a cell belongs to?
Thanks for the addendum.
-- Regards, Tom Ogilvy "Jim Thomlinson" wrote: I think you need to add Dim (or the line wrapped)... Dim definedName As Name Otherwise it is better code in all respects. -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: To the OP To avoid 1004 errors, you might add code check that the two ranges are on the same worksheet before try to intersect them. For example Intersect(worksheets(1).Rows(1),worksheets(2).colu mns(1)).Address raises a 1004 error. Another approach is to error trap. Here is some code I posted back in October 1999 (slightly modified) that demonstrates that approach: Sub TestName() Dim rng As Range definedName as Name On Error Resume Next For Each definedName In ThisWorkbook.Names Set rng = Nothing Set rng = definedName.RefersToRange If Not rng Is Nothing Then If Not Intersect(rng, ActiveCell) Is Nothing Then MsgBox ActiveCell.Address & " is in a range named: " & definedName.Name End If End If Next End Sub This has the advantage that it accounts for defined names that are not ranges as well. (a defined name does not have to refer to a range). -- Regards, Tom Ogilvy "Jim Thomlinson" wrote: Give this a try... Sub test() Dim nme As Name Dim rng As Range Set rng = Range("D4") For Each nme In ThisWorkbook.Names If Not Intersect(rng, nme.RefersToRange) Is Nothing Then MsgBox nme.Name Next nme End Sub -- HTH... Jim Thomlinson "Charis Custard" wrote: I'm tring to find code which allows me to verify which named ranges any single cell belongs to |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find all named ranges in VB.NET? | Excel Discussion (Misc queries) | |||
How do I find all named ranges in VB.NET? | Excel Programming | |||
Named Cell Ranges | Excel Discussion (Misc queries) | |||
Given a cell, find the named range it belongs to | Excel Programming |