need to determine what named range a cell is in

[Ted McCoy]

I need some help. I have 25 named ranges in single worksheet that do not
overlap. I need to be able to determine which of the 25 ranges the
activecell cell is in so that I can move the activecell to the 3rd cell of
that range. I explored using Intersect(rng1,rng2), but rng2 is the very
thing that I am trying to determine. I can't think of an effective way to
loop thru the 25 named ranges to determine which it is either (there are a
bunch of other unrelated named ranges in the workbook as well). I am sure
that I must be missing something simple, but can't see it for the life of
me. Any assistance would be greatly appreciated.

Thanks,
TM

 

[Chip Pearson]

Ted,

Try some code like the following:

Function NameOfRangeOfActiveCell() As String
Dim NM As Name
Dim RR As Range

On Error GoTo 0
For Each NM In ThisWorkbook.Names
Set RR = NM.RefersToRange
If Err.Number = 0 Then
If Not Application.Intersect(ActiveCell, RR) Is Nothing Then
NameOfRangeOfActiveCell = NM.Name
Exit Function
End If
End If
Err.Clear
Next NM
NameOfRangeOfActiveCell = vbNullString
End Function

This will return the name of the (first) range that contains the active
cell. If the active cell is not in any named range, it returns vbNullString.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Ted McCoy]

Chip,

Thank-you very much. I gave this a quick try and am getting a runtime error
on the 'If Not Application.Intersect(ActiveCell, RR) Is Nothing Then' line.
I will take a deeper look tomorrow and see if I can figure it out. BTW, I
will take this opportunity to thank you for the ImportBigFiles module that
you made available on your website. I use it at work regularly and it saves
me alot of time. I appreciate your generosity and the quick response to my
question today.

Thanks again,
TM

 

[Alan]

Probably not the best way, but you could select the whole range:

Range("YourRange").Select ' This places the active cell in the top left
corner of the selected range.
ActiveCell.Offset(0,3).Select 'This will work if you know the offset from
the upper left cell of the range to your destination cell.

Regards,

Alan

 

[Bob Phillips]

You might have a problem with some system names. Try this slightly amended
version

Function NameOfRangeOfActiveCell() As String
Dim NM As Name
Dim RR As Range

On Error GoTo 0
For Each NM In ThisWorkbook.Names
If Not Not NM.Name Like "*_FilterDatabase" _
And Not NM.Name Like "*Print_Area" _
And Not NM.Name Like "*Print_Titles" _
And Not NM.Name Like "*wvu.*" _
And Not NM.Name Like _
"*wrn.*"" AND" Then
Set RR = NM.RefersToRange
If Err.Number = 0 Then
If Not Application.Intersect(ActiveCell, RR) Is Nothing Then
NameOfRangeOfActiveCell = NM.Name
Exit Function
End If
End If
Err.Clear
End If
Next NM
NameOfRangeOfActiveCell = vbNullString
End Function


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Sorry too many NOTs

Function NameOfRangeOfActiveCell() As String
Dim NM As Name
Dim RR As Range

On Error GoTo 0
For Each NM In ThisWorkbook.Names
If Not NM.Name Like "*_FilterDatabase" _
And Not NM.Name Like "*Print_Area" _
And Not NM.Name Like "*Print_Titles" _
And Not NM.Name Like "*wvu.*" _
And Not NM.Name Like _
"*wrn.*"" AND" Then
Set RR = NM.RefersToRange
If Err.Number = 0 Then
If Not Application.Intersect(ActiveCell, RR) Is Nothing Then
NameOfRangeOfActiveCell = NM.Name
Exit Function
End If
End If
Err.Clear
End If
Next NM
NameOfRangeOfActiveCell = vbNullString
End Function



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary Keramidas]

wouldn't something like this work?

Sub test()
Dim nm As Name
Dim a As Range, b As String
For Each nm In ThisWorkbook.Names
If Not Application.Intersect(Range(ActiveCell.Address), _
Range(Range(nm).Address)) Is Nothing Then
MsgBox nm.Name
End If
Next
End Sub