accessing named range

[greg]

if I have an excel named range. Lets say my_range. and it is the range of
$B$11:$B$51
How can I get the value of the first item?
lets say
B11 = AAA
B12 = BBB
etc....

 

[Bob Phillips]

=INDEX(my_range,1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[greg]

thanks,
but not from a cell. but inside of vba

=INDEX(my_range,1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

msgbox worksheets("Sheet999").range("my_Range").cells(1).value

 

[greg]

I see this:
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("my_range")
then rng has lots of things:
Value2
formula
but it does not seem like i can use them.
any help?

 

[Bob Phillips]

Range("my_range").Cells(1,1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Alan Beban]

Or slightly more efficiently

msgbox Worksheets("Sheet999").Range("my_Range")(1).value

Alan Beban

 

[Alan Beban]

Range("my_range").Cells(1,1)

Slightly more efficiently

Range("my_range")(1,1)

or, for the vertical range proposed

Range("my_range")(1)

Alan Beban

 

[Gary Keramidas]

don't know what's more efficient, but just another way

Range("my_range").Range("A1")

 

[Bob Phillips]

Yeah, but ugly, ugly, and not worth any performance gain, so I will not be
using it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[greg]

thanks all for the help
I was thinking maybe i needed to use vlookup.

 

[Alan Beban]

Yeah, but ugly, ugly, and not worth any performance gain, so I will not be
using it.

Interesting comment; "not worth any performance gain" makes it sound as
though there is some giveup in using the format that almost perfectly
mirrors array indexing (highlighting the close relationship between
arrays and ranges), that is the same Item indexing method that is used
in referring to the members of any collection (e.g., Worksheets(1), the
well-established --ugly?--shorthand for Worksheets.Item(1)), and takes
fewer keystrokes to boot. Precisely what is the giveup?

I guess one man's ugliness is another's elegance; I happen to find the
overuse of the almost (not quite) superfluous and relatively inefficient
Cells method as somewhat ugly. C'est la vie.

Alan Beban

 

[Bob Phillips]

But ranges are not a collection, so you are trying to make something look
like something else. A close relationship does not make them the same. Far
better to follow the object model in my book.

And what is the big deal on a few keystrokes. Most commands can be
auto-completed, so a few keystrokes in the grand scheme of writing a serious
app is irrelevant, it is not a race.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Alan Beban]

But ranges are not a collection, so you are trying to make something look
like something else.

Are you suggesting that Range("my_range") is not a collection of cells?
Where is the specification that makes that clear? Precisely how does it
differ from a collection of cells? I'm not trying to make
Range("my_range") look like anything but exactly what it is.

A close relationship does not make them the same. Far

better to follow the object model in my book.

How does Range("my_range").Item(1) deviate from the object model? That's
all that Range("my_range")(1) is, just as Worksheets(1) is the shorthand
for Worksheets.Item(1).

And what is the big deal on a few keystrokes. Most commands can be
auto-completed, so a few keystrokes in the grand scheme of writing a serious
app is irrelevant, it is not a race.

It's not a big deal; it's a small advantage just like the increased
speed of execution is. But it's a bit baffling to me that a serious
programmer would bypass those features without being able, apparently,
to articulate a single advantage for the more cumbersome form.

Alan Beban