Hi,

I would like Excell to give me a range of cells for a value.
Sort of like VLOOKUP, only when Excell finds the item, it will not giv
me a value for it, but a range of cells that associated with this item
This range will vary in length for each item.

For example when it finds the word: "VCR" in cell A4, it will give m
the range that is associated with it: C5:E9.
and when it finds the word: "TV" in cell A11, it will give me the rang
that is associated with it: C12:G40.

As you can see, the range is not the same for each one of these items.


Is there a VBA function I need to write in order for excell to do this

--
Message posted from http://www.ExcelForum.com

Hi
your example is a little bit strange as I do not know how you come from
cell A4 to the range C5:E9. If you can explain this algortihm a
solution may be provided to you.


--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I would like Excell to give me a range of cells for a value.
Sort of like VLOOKUP, only when Excell finds the item, it will not
give me a value for it, but a range of cells that associated with
this item. This range will vary in length for each item.

For example when it finds the word: "VCR" in cell A4, it will give me
the range that is associated with it: C5:E9.
and when it finds the word: "TV" in cell A11, it will give me the
range that is associated with it: C12:G40.

As you can see, the range is not the same for each one of these
items.


Is there a VBA function I need to write in order for excell to do
this?


---
Message posted from http://www.ExcelForum.com/

Hi Frank

It's just like in VLOOKUP. When excell finds a certain text, ie "VCR
it will return the range, which will always start one raw down an
three columns across. The range varies for each item, it will alway
have the same number of columns, however a differenct number of raws i
it.

I have attached a file to explain what I need.


ronenpl wrote:
*Hi,

I would like Excell to give me a range of cells for a value.
Sort of like VLOOKUP, only when Excell finds the item, it will no
give me a value for it, but a range of cells that associated wit
this item. This range will vary in length for each item.

For example when it finds the word: "VCR" in cell A4, it will give m
the range that is associated with it: C5:E9.
and when it finds the word: "TV" in cell A11, it will give me th
range that is associated with it: C12:G40.

As you can see, the range is not the same for each one of thes
items.

Is there a VBA function I need to write in order for excell to d
this?

Attachment filename: range test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45856
--
Message posted from http://www.ExcelForum.com

Hi
not sure what you want with this returned range. But to give you
anexample: If you want to sum this range you may use the following
array entered (CTRL+SHIFT+ENTER) formula (Assumption: F1 stores the
lookup item, e.g. "vcr")

=SUM(OFFSET($C$1:$E$1,MATCH(F1,$A$1:$A$1000,0),0,M ATCH(TRUE,ISBLANK(OFF
SET($C$1,MATCH(F1,$A$1:$A$1000,0),0,100)),0)-1))


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank

It's just like in VLOOKUP. When excell finds a certain text, ie
"VCR"
it will return the range, which will always start one raw down and
three columns across. The range varies for each item, it will
always
have the same number of columns, however a differenct number of raws
in it.

I have attached a file to explain what I need.


ronenpl wrote:
*Hi,

I would like Excell to give me a range of cells for a value.
Sort of like VLOOKUP, only when Excell finds the item, it will not
give me a value for it, but a range of cells that associated with
this item. This range will vary in length for each item.

For example when it finds the word: "VCR" in cell A4, it will give
me
the range that is associated with it: C5:E9.
and when it finds the word: "TV" in cell A11, it will give me the
range that is associated with it: C12:G40.

As you can see, the range is not the same for each one of these
items.

Is there a VBA function I need to write in order for excell to do
this? *

Attachment filename: range test.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=458568 ---
Message posted from http://www.ExcelForum.com/

Hi Frank,

Thanks alot. You helped me alot

--
Message posted from http://www.ExcelForum.com