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

Selecting a defined Name via a cell's contents
 

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

Selecting a defined Name via a cell's contents
 

one way:

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

another:

ActiveWorkbook.Names(Range("A2").Value).RefersToRa nge.Select


In article ,
"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

Selecting a defined Name via a cell's contents
 

It appears to work in Excel 2000 as well.

Thank you.
Paul


"Toppers" wrote in message
...
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

Selecting a defined Name via a cell's contents
 

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" wrote in message
...
one way:

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

another:

ActiveWorkbook.Names(Range("A2").Value).RefersToRa nge.Select


In article ,
"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

Selecting a defined Name via a cell's contents
 

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" wrote in message
...
one way:

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

another:

ActiveWorkbook.Names(Range("A2").Value).RefersToRa nge.Select


In article ,
"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

Selecting a defined Name via a cell's contents
 

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 ,
"PCLIVE" 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.

Selecting a defined Name via a cell's contents
 

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" wrote in message
...
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 ,
"PCLIVE" 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.

Selecting a defined Name via a cell's contents
 

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 ,
"PCLIVE" 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?

Selecting a defined Name via a cell's contents
 

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" wrote in message
...
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 ,
"PCLIVE" 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?