Using dynamic ranges – is this next step possible?

[aquixano]

I’ve got vba code which dynamically redefines a range and assigns it to a defined range name on a worksheet. Then I used INDIRECT to use the range name in various formulas as follows – note how I assign the range to range name “iStopList” and then use it in a worksheet formula.

Please Login or Register  to view this content.

Two questions. 1) Is it possible to use the range defined on a worksheet in a vba line of code? For instance, if LRowST=69, rather than rather than writing the first line of code, could I used the range name as in the second example using some equivalent to INDIRECT?

Please Login or Register  to view this content.

2) If that is possible, is there a way to use something like OFFSET so that I could use iStopList as the anchor but select the cells in the next column to the right ("L6:L69")?
I haven’t been able to figure it out / or find anything on the web. Thanks for reading and I appreciate any help.

[protonLeah]

You explanation is a little foggy, but:

Please Login or Register  to view this content.

debug window:
IstopList address: $K$6:$K$69
offset address is: $L$6:$L$69

[aquixano]

protonLeah – sorry my explanation must have been more than a little foggy to send you off track. Let me try again. I have created and attached an example workbook. I have vba that creates the range addresses stored in column L of the worksheet (not included). The workbook has a sample macro to show how I currently define the range in VBA to copy selected cells (I’m not really copying, but this is an easy example to show what I’m trying to do). What I’m currently doing works fine, but it seems like I’m somewhat recreating the wheel since I’m basically redefining the range twice – once for use in worksheet formulas (the original purpose of putting them in worksheet cells) and once in VBA to do other non-worksheet formula manipulation.

I’ve added comments in the macro to hopefully clarify my original questions. Hope this will help you cut through my fog – thanks for your help.

[protonLeah]

...I have already saved the range definition in cell T2 (range name "iStopList") on Worksheet "Initial Stops"...
...Q1 is where it's possible to use that definition in vba such as...

Please Login or Register  to view this content.

...This doesn't work - only copies cell T2 and I cannot find an equivalent to the worksheet function of Indirect for use in VBA. ...

One Option:

Please Login or Register  to view this content.

'Q2 - if Q1 is possible can I use it to identify the same cells in column L or would I need to create a new name range cell on the worksheet to store L2:L12"

Please Login or Register  to view this content.

[aquixano]

Perfect! I figured there was a way to do this – only required someone smarter than me to turn the light on in the room. Thanks for your assistance!