Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select multiple named ranges with macro
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select multiple named ranges with macro
Try the below Dim varName(1 To 3) varName(1) = "Name1" varName(2) = "Name2" varName(3) = "Name3" strname = Join(varName, ",") Range(strname).Select If this post helps click Yes --------------- Jacob Skaria "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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select multiple named ranges with macro
OR try Range(vSelections(1) & "," & vSelections(2) & "," & vSelection(3)).Select If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below Dim varName(1 To 3) varName(1) = "Name1" varName(2) = "Name2" varName(3) = "Name3" strname = Join(varName, ",") Range(strname).Select If this post helps click Yes --------------- Jacob Skaria "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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select multiple named ranges with macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select multiple named ranges with macro
There could be a bug in that second routine -- depending on how you used those names. Sub testme2() Dim iCtr As Long Dim myArr As Variant Dim myRng As Range Dim myCell As Range Set myCell = ActiveCell 'this replaces your code that determines the array of names myArr = Array("test1", "test2", "test3", "test5") Set myRng = mycell.parent.range(myarr(lbound(myarr))) For iCtr = LBound(myArr) + 1 To UBound(myArr) Set myRng = Union(myRng, myCell.Parent.Range(myArr(iCtr))) Next iCtr MsgBox myRng.Address(external:=True) 'just for testing Application.Goto reference:=myRng, scroll:=False End Sub mycell.parent is the worksheet that owns the cell in question. If you used workbook level names and worksheet level names, then the ..referstorange may not be on the correct worksheet. Dave Peterson wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting multiple named ranges | Excel Programming | |||
Named Ranges - Multiple Worksheets | Excel Discussion (Misc queries) | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Multiple areas in named ranges | Excel Programming |