Maybe you could drop the array of names and just build a union of ranges:
Option Explicit
Sub testme()
Dim nm As Name
Dim TestRng As Range
Dim myCell As Range
Dim myRng As Range
Set myCell = ActiveCell
For Each nm In ActiveWorkbook.Names
Set TestRng = Nothing
On Error Resume Next
Set TestRng = nm.RefersToRange
On Error GoTo 0
If TestRng Is Nothing Then
'do nothing
Else
If TestRng.Parent.Name < myCell.Parent.Name Then
'not on the same worksheet, do nothing
Else
If Intersect(TestRng, myCell) Is Nothing Then
'cell not in the range, do nothing
Else
If myRng Is Nothing Then
Set myRng = TestRng
Else
Set myRng = Union(myRng, TestRng)
End If
End If
End If
End If
Next nm
If myRng Is Nothing Then
MsgBox "no names contain this cell"
Else
MsgBox myRng.Address(external:=True) 'just for testing
Application.Goto reference:=myRng, scroll:=False
End If
End Sub
============
But if you had that array, you could use something like:
Sub testme2()
Dim iCtr As Long
Dim myArr As Variant
Dim myRng As Range
'this replaces your code that determines the array of names
myArr = Array("test1", "test2", "test3", "test5")
Set myRng = ActiveWorkbook.Names(myArr(LBound(myArr))).RefersT oRange
For iCtr = LBound(myArr) + 1 To UBound(myArr)
Set myRng = Union(myRng, _
ActiveWorkbook.Names(myArr(iCtr)).RefersToRange)
Next iCtr
MsgBox myRng.Address(external:=True) 'just for testing
Application.Goto reference:=myRng, scroll:=False
End Sub
============
Before you spend too much time creating your own name utility, you may want to
look at Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:
You can find it at:
NameManager.Zip from
http://www.oaltd.co.uk/mvp
It has this feature built in--and lots of other good stuff, too.
Ted M H wrote:
I have a macro that looks at the active cell and determines the named
ranges that it is a part of. It can be one named range or many. I
store the names in an array, one name per element in the array.
After populating the array with the names, I want to select all of the
named ranges that have been stored in the array.
I can do this easily like this:
Range(mg_benefits, mg_FY11,mg_fy12).select
but when I try to do the same thing from my array it
doesnt work.
My array is: Dim vSelections(1 to 100) as Variant
I fill the first three elements with the names above. Ive tried
Range("vSelections(1), vSelections(2),vSelection(3)").Select
and
Range(vSelections(1), vSelections(2) ,vSelection(3)).Select
But these dont work. I think Im missing something simple, but I
cant figure it out. Any suggestions?
--
Dave Peterson