How do you access named ranges via VB when you don't know what sheet they are?

Experts,

I have a worksheet with a bunch of named ranges in it and am having trouble selecting these ranges via:

For i = 1 To activeworkbook.Names.Count
    range(activeworkbook.Names(i).refersto).select
next i

The only way this works is if the sheet that the range refers to is active.   This defeats the purpose of the named range!  Otherwise I get "Run time error '1004' - Select method of range failed".

This is killing me because I can see the sheet name in the 'refersto', and could parse it out and activate it, but this seems wrong.  I have played with using .value, .refersto, .name, but none work.  But I would like to access these ranges regadless of what sheet is currentlty active.

Thanks,
Brian
BrianEKramerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

R_RajeshCommented:
Hi BrianEKramer,

you can't select or activate a range unless it happens to be in the active sheet
but you can access it no matter which sheet is active

For Each c In Range(ActiveWorkbook.Names(1).RefersTo)
MsgBox c
Next c

Range(ActiveWorkbook.Names(1).RefersTo).Interior.ColorIndex = 5

Rajesh
BrianEKramerAuthor Commented:
Then is there an easy way to activate the sheet?  For example, is there an easy way to get the sheet name where the range resides other than parsing it out of the 'refersto' and manually activating it?
Robberbaron (robr)Commented:
If the named range is unique to the Workbook it can be used globally.

ie range("test1").columns.count

if the same range name is used on multiple sheets, then each must be referred to with a sheet reference.
ie range("Sheet1!test1").columns.count

Why do you need to 'select'  ? this just highlights the range.

Or do you really want the RefersToRange property, which returns a range associated with a name.

dim rngp as Range
For i = 1 To activeworkbook.Names.Count
    rngp=ActiveWorkbook.Names(i).RefersToRange

    rngp.select ' or whatever you want.
next i
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

BrianEKramerAuthor Commented:
This doesn't work.  I get 'select method of range class failed'.  My code:

Set curbk = ActiveWorkbook
Dim rr As Range
For i = 1 To curbk.Names.Count
        curbk.Activate
        Set rr = curbk.Names(i).RefersToRange
       rr.Select
Next i

Robberbaron (robr)Commented:
i get the same problem. sorry for leading you astray. dont quite know why.

here is a snippet that works. I had to activate the correct sheet, much as you had considered

Set curbk = ActiveWorkbook
Dim rr As Range
curbk.Activate
For i = 1 To curbk.Names.Count
       
        Set rr = curbk.Names(i).RefersToRange
        curbk.Sheets(rr.Parent.Name).Activate
        rr.Select
Next i
Robberbaron (robr)Commented:
so long as you dont want to 'select' the range, you can access the data and formatting ok.

Are you sure you need to 'select' the range for your app ?
otherwise, you can access the range via the rr object above.
byundtMechanical EngineerCommented:
Brian,
A simpler way of going to a named range on a different worksheet uses the GoTo method:
Application.GoTo Range("myNamedRange")
Application.GoTo Range(ActiveWorkbook.Names(i).Name)

Or you could create a range variable pointing to it with:
Set rr=Range(ActiveWorkbook.Names(i).Name)

Brad

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
coljeCommented:
If all you want to do is access them then you can:-

'Get first cell in the range
strValue = Application.Range("myNamedRange").cells(1,1).value

'Write to a cell in the range
Application.Range("myNamedRange").cells(1,1).value = "A String"

or

'Assign contents to 2d array
Dim arrValues as Variant
arrValues = Application.Range("myNamedRange").value

BrianEKramerAuthor Commented:
Here's some code I glommed together that seems to work.  It's convoluted (see curbk.Sheets(curbk.Names(i).RefersToRange.Parent.name).Activate), and I have to think there is a better way, but it works.

Thanks for all your help.  Everyone had a piece, so I'm splitting the points.
Brian

==========
Sub list_named_ranges()

Set curbk = ActiveWorkbook
curbk.Activate

    For i = 1 To curbk.Names.Count
        curbk.Activate
        curbk.Sheets(curbk.Names(i).RefersToRange.Parent.name).Activate
        curbk.Names(i).RefersToRange.Select
        MsgBox curbk.Names(i).name & "    " & curbk.Names(i)   'print range name and address
    Next i

End If

End Sub
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.