Finding a range in a formula

[DiCaver]

Hi,

I'm looking for a macro/line code, which will recognise a range of cells in a formula. Let's say that I have in A1 formula:

- =SUM(B2:F17) or
- =VLOOKUP(B1;$B$1:$D$15;3;FALSE) or
- ={TRANSPOSE(K3:HI236)} or
- ...

So as you can see, I'm talking about different sizes, types, locations (in formula), ... of ranges. The only thing they have in common is ":" between cell references.

Now I need a macro, that will look into the cell A1, read the formula in the cell and return me a range of cells in this formula. As seen in paragraph above, for first line it will return B2:F17, second $B$1:$D$15, third K3:HI236, ...

I was thinking to use "find :", but then I don't know how to tell how far to go on the left/right to read complete cell reference. I could use ISTEXT or ISNUMBER but it will stop at the first letter/number and as you can see I can have text HI or number 234. I can't use "find (", because sometimes range isn't next to "(" or ")" ... like in VLOOKUP example.

Maybe a macro that it will found upper-left and bottom-right cell in range and then I can put together a range ... anything that comes to your mind will help me.


Thanks in advance, Marko