Selecting a defined Name via a cell's contents

I have this.

Range("Green").Select


"Green" is a define name of a cell range.
If "Green" is also the contents of cell A2, how can I rewrite the Select
statement so that it will refer to the defined name range that is in cell
A2?

Thanks,
Paul

Range(Range("A2")).select works in XL2003

"PCLIVE" wrote:

> I have this.
>
> Range("Green").Select
>
>
> "Green" is a define name of a cell range.
> If "Green" is also the contents of cell A2, how can I rewrite the Select
> statement so that it will refer to the defined name range that is in cell
> A2?
>
> Thanks,
> Paul
>
>
>

one way:

Range(Range("A2").Value).Select

another:

ActiveWorkbook.Names(Range("A2").Value).RefersToRange.Select


In article <[email protected]>,
"PCLIVE" <[email protected]> wrote:

> I have this.
>
> Range("Green").Select
>
>
> "Green" is a define name of a cell range.
> If "Green" is also the contents of cell A2, how can I rewrite the Select
> statement so that it will refer to the defined name range that is in cell
> A2?
>
> Thanks,
> Paul

It appears to work in Excel 2000 as well.

Thank you.
Paul


"Toppers" <[email protected]> wrote in message
news:[email protected]...
> Range(Range("A2")).select works in XL2003
>
> "PCLIVE" wrote:
>
>> I have this.
>>
>> Range("Green").Select
>>
>>
>> "Green" is a define name of a cell range.
>> If "Green" is also the contents of cell A2, how can I rewrite the Select
>> statement so that it will refer to the defined name range that is in cell
>> A2?
>>
>> Thanks,
>> Paul
>>
>>
>>

JE,

I respect your advice as you have helped me many times before. I didn't
try your second suggestion since the first one works and seems a bit
simpler. However, I thought you might explain something to me. Is there
any advantage or disadvantage between your suggested method and Toppers
suggestion?

Toppers' suggestion: Range(Range("A2")).select
Your suggestion: Range(Range("A2").Value).Select

There both very similar and seem to work. Does ".Value" give any added
accuracy or stability, or is it just another way to write it.

Thanks again for all of your help.
Paul

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> one way:
>
> Range(Range("A2").Value).Select
>
> another:
>
> ActiveWorkbook.Names(Range("A2").Value).RefersToRange.Select
>
>
> In article <[email protected]>,
> "PCLIVE" <[email protected]> wrote:
>
>> I have this.
>>
>> Range("Green").Select
>>
>>
>> "Green" is a define name of a cell range.
>> If "Green" is also the contents of cell A2, how can I rewrite the Select
>> statement so that it will refer to the defined name range that is in cell
>> A2?
>>
>> Thanks,
>> Paul

JE,

I've got one more thing that I need help with.

This is still regarding defined name ranges.

Selection.Sort Key1:=Range("AJ2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Im using the above code to sort. however, I need to base the 'SortKey1' on
the first column of the selected unknown defined name range.
Without knowing that AJ2 is the first column of the define name range that
is selected (which was selected using the previous suggestion
Range(Range("A2").Value).Select ), I need to sort based on the first
column of the selected range

Can this be done?

Thanks again,
Paul


"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> one way:
>
> Range(Range("A2").Value).Select
>
> another:
>
> ActiveWorkbook.Names(Range("A2").Value).RefersToRange.Select
>
>
> In article <[email protected]>,
> "PCLIVE" <[email protected]> wrote:
>
>> I have this.
>>
>> Range("Green").Select
>>
>>
>> "Green" is a define name of a cell range.
>> If "Green" is also the contents of cell A2, how can I rewrite the Select
>> statement so that it will refer to the defined name range that is in cell
>> A2?
>>
>> Thanks,
>> Paul

As far as VBA's concerned, there's no particular advantage or
disadvantage to either. Topper's suggestion is obviously shorter.

I try to be explicit with the properties I reference. The .Value
property is the default property for the Range object, so

Dim a As Double
a = Range("A2")

and

Dim a As Double
a = Range("A2").Value

are equivalent - the context implies that the .Value property is what is
being referred to in the first example, not the object itself. I find
that making the property explicit helps make the code more readable and
maintainable.

YMMV.



In article <[email protected]>,
"PCLIVE" <[email protected]> wrote:

> Is there
> any advantage or disadvantage between your suggested method and Toppers
> suggestion?
>
> Toppers' suggestion: Range(Range("A2")).select
> Your suggestion: Range(Range("A2").Value).Select
>
> There both very similar and seem to work. Does ".Value" give any added
> accuracy or stability, or is it just another way to write it.

Thanks,
I agree with you. It gives a better sense of what was being targeted when
the code was written.
Thanks for the detailed explanation.

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> As far as VBA's concerned, there's no particular advantage or
> disadvantage to either. Topper's suggestion is obviously shorter.
>
> I try to be explicit with the properties I reference. The .Value
> property is the default property for the Range object, so
>
> Dim a As Double
> a = Range("A2")
>
> and
>
> Dim a As Double
> a = Range("A2").Value
>
> are equivalent - the context implies that the .Value property is what is
> being referred to in the first example, not the object itself. I find
> that making the property explicit helps make the code more readable and
> maintainable.
>
> YMMV.
>
>
>
> In article <[email protected]>,
> "PCLIVE" <[email protected]> wrote:
>
>> Is there
>> any advantage or disadvantage between your suggested method and Toppers
>> suggestion?
>>
>> Toppers' suggestion: Range(Range("A2")).select
>> Your suggestion: Range(Range("A2").Value).Select
>>
>> There both very similar and seem to work. Does ".Value" give any added
>> accuracy or stability, or is it just another way to write it.

First, are you using the defined name range or the Selection? There's no
need to select anything:

With Range(Range("A2").Value)
.Sort Key1:=.Cells(1,1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With



(and no, there's no reason to use continuation lines rather than putting
it all on one line - I just find it more readable).

In article <[email protected]>,
"PCLIVE" <[email protected]> wrote:

> JE,
>
> I've got one more thing that I need help with.
>
> This is still regarding defined name ranges.
>
> Selection.Sort Key1:=Range("AJ2"), Order1:=xlAscending, Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
>
> Im using the above code to sort. however, I need to base the 'SortKey1' on
> the first column of the selected unknown defined name range.
> Without knowing that AJ2 is the first column of the define name range that
> is selected (which was selected using the previous suggestion
> Range(Range("A2").Value).Select ), I need to sort based on the first
> column of the selected range
>
> Can this be done?

I am using a defined name range. And you are correct as usual. Your code
is much prettier than what I was using. I have updated my code to mirror
your suggestions.
It works great!

Thanks for everything.
Paul

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> First, are you using the defined name range or the Selection? There's no
> need to select anything:
>
> With Range(Range("A2").Value)
> .Sort Key1:=.Cells(1,1), _
> Order1:=xlAscending, _
> Header:=xlNo, _
> OrderCustom:=1, _
> MatchCase:=False, _
> Orientation:=xlTopToBottom
> End With
>
>
>
> (and no, there's no reason to use continuation lines rather than putting
> it all on one line - I just find it more readable).
>
> In article <[email protected]>,
> "PCLIVE" <[email protected]> wrote:
>
>> JE,
>>
>> I've got one more thing that I need help with.
>>
>> This is still regarding defined name ranges.
>>
>> Selection.Sort Key1:=Range("AJ2"), Order1:=xlAscending, Header:=xlNo,
>> _
>> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
>>
>> Im using the above code to sort. however, I need to base the 'SortKey1'
>> on
>> the first column of the selected unknown defined name range.
>> Without knowing that AJ2 is the first column of the define name range
>> that
>> is selected (which was selected using the previous suggestion
>> Range(Range("A2").Value).Select ), I need to sort based on the first
>> column of the selected range
>>
>> Can this be done?