VBA to define range without specifying sheet

[esbencito]

Hi all,

Is there a way to define a range as my "TimePeriod", let's say $AL$5:$AL$1500 without specifying the sheet? I would need to use the exact same range on different sheets, so I want to specify which sheet it should refer to within the formula I'm using.

e.g

original formula:

Please Login or Register  to view this content.

adjusted formula with defined range:

Please Login or Register  to view this content.

my range is defined in the following VBA code which is linked to my form control box where one can select the time period:

Please Login or Register  to view this content.

As you can see, in the above code, the range is still defined by referring to a particular sheet (Location Plan) though, I want it to only define the range and then specify the sheet it should refer to in the formula I mentioned at the top.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Something like the above...

Is that even feasible? (The above doesn't work...)

[xlnitwit]

Hi,

It's not possible with just a defined name. You can define a name to work in the sheet it's called from, but not in such a way that you can specify the worksheet when calling it, unless you use a UDF or INDIRECT.

[peterrc]

Hi esbencito,

Is the data in the "TimePeriod" range different on each sheet?
How many sheets are there?

Regards

peterrc

[esbencito]

Hi esbencito,

Is the data in the "TimePeriod" range different on each sheet?
How many sheets are there?

Regards

peterrc

The data is indeed different on each sheet and there are about 4 sheets...

[esbencito]

Hi,

It's not possible with just a defined name. You can define a name to work in the sheet it's called from, but not in such a way that you can specify the worksheet when calling it, unless you use a UDF or INDIRECT.

How would a UDF or INDIRECT look like?

[xlnitwit]

You might define the TimePeriod name as
="$AL$5:$AL$1500"
and then use
=INDIRECT("'Location Plan'!"&timeperiod)
to refer to the range on a specific sheet. This will be a volatile function but that is largely unavoidable with your approach.

[esbencito]

Please Login or Register  to view this content.

so the above code would be adjusted to the following?

Please Login or Register  to view this content.

And then my formula that refers to that range would look like below?

Please Login or Register  to view this content.

correct? or did I forget something? As it doesn't seem to work (yet). Did you try it with a similar example?

[xlnitwit]

Not quite- your name needs to contain a string not a range, thus

Please Login or Register  to view this content.

[esbencito]

Please Login or Register  to view this content.

I see! and how would I fixed the part of the code with the "Offset"? It's showing me a syntax error for these...

[xlnitwit]

You'll need to use a range for the offset and then get its address

Please Login or Register  to view this content.

[esbencito]

That works incredibly well!! Thanks so much!

[xlnitwit]

You're welcome. Thank you for the rep.