Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A cell is in which named range?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A cell is in which named range?
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) "MJKelly" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A cell is in which named range?
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 -- Gary''s Student - gsnu2007h "MJKelly" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A cell is in which named range?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A cell is in which named range?
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. -- Gary''s Student - gsnu200786 "MJKelly" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A cell is in which named range?
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("A1:D4"), Range("C3:F8")) returns a reference to the range C3:D4 since those cells a common to both A1:D4 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 A1:D4 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
A cell is in which named range?
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
A cell is in which named range?
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 -- Gary''s Student - gsnu200786 "MJKelly" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
named cell range | Excel Worksheet Functions | |||
If any cell in named range = 8 then shade named range | Excel Programming | |||
First and Last cell of a named range | Excel Programming |