VBA function for having excel find range automatically.

  • Thread starter Thread starter ronenpl
  • Start date Start date
R

ronenpl

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

*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?
Click to expand...

Attachment filename: range test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45856
 
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,MATCH(TRUE,ISBLANK(OFF
SET($C$1,MATCH(F1,$A$1:$A$1000,0),0,100)),0)-1))
 
Back
Top