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....

=INDEX(my_range,1)

--
HTH

Bob

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

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

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


"Bob Phillips" wrote in message
...
=INDEX(my_range,1)

--
HTH

Bob

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

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

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



greg wrote:

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....

--

Dave Peterson

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?




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

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

--
---
HTH

Bob

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



"greg" wrote in message
...
thanks,
but not from a cell. but inside of vba


"Bob Phillips" wrote in message
...
=INDEX(my_range,1)

--
HTH

Bob

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

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

Or slightly more efficiently

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

Alan Beban

Dave Peterson wrote:
msgbox worksheets("Sheet999").range("my_Range").cells(1). value



greg wrote:
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 wrote:
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

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

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


Gary


"Alan Beban" <unavailable wrote in message
...
Bob Phillips wrote:
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

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)

"Alan Beban" <unavailable wrote in message
...
Bob Phillips wrote:
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

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


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

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" <unavailable wrote in message
...
Bob Phillips wrote:
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 wrote:
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