Error to retrieve range associated with "named range"

[cgkmal]

Hello to all,

I have and named range called "Colors" associated with range "Sheet2!$B1:$B4" and I get error
"Run-time1004: Application-defined or Object-defined error" when I use this code to get the range
associated.

Please Login or Register  to view this content.

May somebody help me to fix this please. I'm attaching a sample file.

Regards

[Tinbendr]

If all you're trying to do is assign the Color range to a variable then

Please Login or Register  to view this content.

[mikerickson]

When assigning Objects to variables, one should use the keyword Set.

Please Login or Register  to view this content.

[cgkmal]

Hello tinbendr and mikerickson,

I've tried both options and continue getting the same error 1004.

Please Login or Register  to view this content.

I don't know what happen. Do these 2 options work for you with my sample file attached in first porst?

Regards

[mikerickson]

When Set is added, that code works fine for me.

[cgkmal]

Uhh, it's strange. I'm using MS Excel 2010 under Windows 7.

Do you know if this should work depends on enabling some library in Excel Advanced options?

[mikerickson]

I'm using Excel 2011.

What does this return for you?

Please Login or Register  to view this content.

[watersev]

as per help in Excel 2007(I assume it is the same story for Excel 2010) for Name.RefersToRange Property:

Remarks
If the Name object doesn't refer to a range (for example, if it refers to a constant or a formula), this property fails.

Si it looks Excel versions after 2010 can evaluate the formula and produce the range object

[Tinbendr]

Both examples work on my Office 2013.

[mikerickson]

When you run that code, what is the string that the computer returns on the OP machine?