A cell is in which named range?

[MJKelly]

Hi,

I am looping through multiple named ranges. Depending which named
range a value is in, I need to reference another named range
(basically Monday, Tuesday etc). How Can I Select Case to find the
named range of the current cell to determin which other named range to
move to? So a cell in MondayData named range will be reported in
MondayReport named range. I see this as an option for me to re-use
the code instead of using seven versions of the code (one for each
day).

Hope you can help?

kind regards,
Matt

 

[Chip Pearson]

You can use code like

Debug.Print ActiveCell.Name.Name

to get the name of the active cell. This, of course, assumes that the name
refers only to that cell, not to a larger range of which ActiveCell is one
cell. If you want to find the larger range(s) that contain some cell, you'll
have to loop through all the Names and use Intersect to see if the
RefersToRange encompasses the ActiveCell. For example,

Function NamesWithCell(R As Range) As Name()
Dim NN() As Name
Dim N As Name
Dim J As Long
On Error Resume Next
For Each N In ThisWorkbook.Names
If Not Application.Intersect(N.RefersToRange, R) Is Nothing Then
J = J + 1
ReDim Preserve NN(1 To J)
Set NN(J) = N
End If
Next N
NamesWithCell = NN
End Function

Private Function IsArrayAllocated(V As Variant) As Boolean
IsArrayAllocated = IsArray(V) And Not IsError(LBound(V, 1)) And
LBound(V) <= UBound(V)
End Function

Sub AAA()
Dim FoundNames() As Name
Dim J As Long
FoundNames = NamesWithCell(ActiveCell)
If IsArrayAllocated(FoundNames) = True Then
For J = LBound(FoundNames) To UBound(FoundNames)
Debug.Print FoundNames(J).Name,
FoundNames(J).RefersToRange.Address
Next J
Else
Debug.Print "no names found"
End If
End Sub

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary''s Student]

Be aware that a single can be in many Named Ranges at the same time if the
ranges over-lap.

if not:

Sub whereAmI()
Set r = ActiveCell
For Each n In ThisWorkbook.Names
If Intersect(r, Range(n)) Is Nothing Then
Else
MsgBox ("the active cell is in range " & n.Name)
End If
Next
End Sub

 

[MJKelly]

Thanks Guys,
I am still quite confused. I am looping through set named ranges (a
cell only appears in one named range). I am not selecting any cells.
So

For each C in ranges (named ranges)
do stuff

How can I determin if C is in named range1 or named range2?

if in named range 1 then put values in named range 3
if in named range 2 then put values in named range 4

Matt

 

[Gary''s Student]

I assumed that when you said:

"find the named range of the current cell"

that you wanted the Named Range of the ActiveCell. If this is not the case,
then just use:

Set r=Range("B9")

or whatever cell you feel is most current.

 

[Chip Pearson]

A Name has a property called RefersToRange which is a reference to the cells
defined in the Name. When you loop through the names, you need to use the
Intersect method to see if some cell, such as the ActiveCell, is within the
Name's RefersToRange. Intersect takes two (or more) ranges and returns as it
result those cells that are in both (all) input ranges. For example,

Application.Intersect(Range("A14"), Range("C3:F8"))

returns a reference to the range C34 since those cells a common to both
A14 and C3:F8. If there are no cells in common, Intersect returns a
special value called Nothing.

Therefore, you can use code like the following.


Sub AAA()
Dim NN As Name
Dim R As Range
For Each NN In ThisWorkbook.Names
Set R = Application.Intersect(NN.RefersToRange, ActiveCell)
If R Is Nothing Then
' no common cells in NN and ActiveCell
Debug.Print "ActiveCell is NOT within Name: " & _
NN.Name & "(" & NN.RefersToRange.Address & ")"
Else
' ActiveCell within NN.RefersToRange
Debug.Print "ActiveCell is within Name: " & _
NN.Name & "(" & NN.RefersToRange.Address & ")"
End If
Next NN
End Sub


This loops through all the Names in the workbook, assigning the Name to the
variable NN, and then uses Intersect between NN.RefersToRange and ActiveCell
to determine whether ActiveCell is within the range defined by NN. In the
code I wrote in my previous reply, I handled the case in which a cell may be
within two named ranges. That's why the code used an array of Name objects.
(E.g., if NameOne is A14 and NameTwo is B2:F4, cell C3 is within both
NameOne and NameTwo.)

The code above examines ALL named ranges in the workbook. If you want only
to examine a few Names, store the names of the Names in an array and loop
through that. E.g.,


Sub BBB()
Dim Arr As Variant
Dim N As Long
Dim R As Range
Arr = Array("NameOne", "NameTwo", "NameThree")
For N = LBound(Arr) To UBound(Arr)
Set R = Application.Intersect(Range(Arr(N)), ActiveCell)
If R Is Nothing Then
Debug.Print "ActiveCell not within range: " & Arr(N)
Else
Debug.Print "ActiveCell is within range: " & Arr(N)
End If
Next N
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[MJKelly]

I'm very sorry, but I still don't understand.

Dim C As Range
For Each C In Worksheets("Agency").Range("Ag_Shifts_Mon,
Ag_Shifts_Tue").Cells

How do I determin if C is in named range Ag_Shifts_Mon or
Ag_Shifts_Tue?

Matt
PS - Many thanks for your help.

 

[Gary''s Student]

Sub dural()
Dim C As Range
Set r1 = Range("Ag_Shifts_Mon")
Set r2 = Range("Ag_Shifts_Tue")
Set r = Union(r1, r2)
For Each C In Worksheets("Agency").r.Cells
If Intersect(C, Range("Ag_Shifts_Mon")) Is Nothing Then
MsgBox (C.Address & " is in Ag_Shifts_Tue")
Else
MsgBox (C.Address & " is in Ag_Shifts_Mon")
End If
Next
End Sub

I have no blank after Mon or Tue