Referring to Named Ranges defined with OFFSET()

Hi, I am fairly inexperienced in VBA and I'm having trouble with getting to the values in a named range in a subroutine.

I have many named ranges in my project, some of which are defined as simple ranges ($A$1:$A$10 for example), and some of which are dynamically defined using OFFSET (for tables which change in length etc.)

For a named range defined as a simple range, I can do the following:

Please Login or Register  to view this content.

Or something like that, anyway, I'm picking bits out of my code to show you. Now, my problem is that if I try to do this with a named range that has been defined using OFFSET, this method doesn't work. I have tried several different ways and I always get some error Can anyone show me how to use such named ranges within VBA?

I am assuming that it's because .RefersToRange requires the Named Range to point to a range directly, but the ones with OFFSET point to a formula... I have tried removing the .RefersToRange completely, and tried it with .RefersTo and neither worked. Is there a way I can, instead of using the OFFSET named range, simply create a reference to the same range using the OFFSET formula within VBA? If so, how do I then get the values out within the For/Next loop?

Thanks for any help, I can provide more info/code if required.

can you rebuild the range?

Please Login or Register  to view this content.

The code you have will work with a valid named range of any type.

Originally Posted by Leon V (AW)

can you rebuild the range?

Please Login or Register  to view this content.

That worked great, thanks!

Originally Posted by Izandol

The code you have will work with a valid named range of any type.

I have tried it several times and it doesn't, I'm afraid. It's annoying as it works fine for ranges defined simply as a range (rather than an offset formula)

May you post an example that does not work?

May you post an example that does not work?

Well, if you set up a named range in a sheet and define it by an offset formula, then try to pass the line:

Please Login or Register  to view this content.

Then it throws an error, unless I've got confused :S

If it raised an error for me I would not ask for an example.

is it a negative offset excel can throw a wobbler trying to go backwards in some areas

Originally Posted by Izandol

If it raised an error for me I would not ask for an example.

Well that was my example I can't explain why it doesn't work.... I noticed that in the Named Ranges manager, it always says that the "value" of my named ranges that I define using OFFSET is = {...} , this may have something to do with it? It doesn't seem to mean that the range is undefined, as I can still refer to it and use it for data validation, and pull data from it using the other methods suggested above.