Finding a range in a formula

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

Looking for a ":" is not good enough, for example:

=PROPER(A1)

has no ":"

also, the $ will complicate things too...

$B$1:$D$15 compared to B1:D15

I was count to try left/mid/right, but it will be a nightmare

Originally Posted by StephenR

vOut is an array but is output to the cell to the right of the one containing your formula. So you could do this

r.offset(,1).Select

if you've only got one range in each formula but will need to resize if you've got more than one.

Thx for fast reply, but your suggestion is going on the actual cell, same cell that is populated with range from formula in r.Offset(, 1).Resize(, i) = vOut line. English is not my 1^st language and probably I didn't explain enough good ...

Let say that in A1 I have =SUM(D1:F1), so line r.Offset(, 1).Resize(, i) = vOut write D1:F1 in cell B1. Good! But now I need this D1:F1 to be Range or String, that I can use as Range("D1:F1").Select.

At the moment I can read a value from "B1" and use it, but I would like to skip those few lines and write "vOut" direct in to Range(). What I’m doing wrong?

Thx, Marko

Originally Posted by StephenR

Marko - not sure I follow. You say "write vOut direct in to Range()" but what range is that? You can put the array anywhere, e.g.

Range("C20").Resize(, i) = vOut

Btw if your formulae only have one range the code can be simplified.

I'm really sorry to bother you ...

Originally Posted by StephenR

... but what range is that?

It is the range that I got it from your macro, it is vOut array, it is the value that is written in "left of r cell".

You write ...

Originally Posted by StephenR

Range("C20").Resize(, i) = vOut

So instead "C20" I would like that is the value/vOut/array/range which is written in "left of r cell"

ActiveSheet.Range(value/vOut/array/range).Select

Originally Posted by StephenR

Btw if your formulae only have one range the code can be simplified.

Yes, mostly formulas have one range, I'll not bother with them if they have (it is rare) two or more.

I need this, because the extracted value/vOut/array/range that I got it from formula with you macro, I need to select and paste as values in the other sheet where they will be manually validated. I'll be using your macro for something like this ...

check if cell (in which is formula) value is the same as some other value
if yes
next cell
if not
your macro
select a range that is used in formula (provided with help of your macro)
copy & paste as values in other sheet
next cell


Hope that this helps you ... cheers, Marko

I figured it out!

This is just temp macro, which now I need to finish, but main work was done by you StephenR! Thing that I was using at last few posts is in function Join(). I added those 3 lines ...

Dim rng As String

rng = Join(vOut, ",")

Range(rng).Copy Destination:=Range(r.Offset(10, 10).Address)

... this line I've removed ...

r.Offset(, 1).Resize(, i) = vOut

... so I ended with this code ...

Sub x()

Dim r As Range, oMatches As Object, i As Long, vOut
Dim rng As String

For Each r In Selection.Cells.SpecialCells(xlCellTypeFormulas)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\$?)[A-Z](\$?)+[0-9]+:(\$?)[A-Z]+(\$?)[0-9]+"
        Set oMatches = .Execute(r.Formula)
    End With
    
    ReDim vOut(0 To oMatches.count - 1)
    
    For i = 0 To oMatches.count - 1
        vOut(i) = oMatches(i).Value
    Next i
    
    rng = Join(vOut, ",")
    
    Range(rng).Copy Destination:=Range(r.Offset(10, 10).Address)
    
Next r

End Sub

Like I wrote, this is temp macro, so line ...

Range(rng).Copy Destination:=Range(r.Offset(10, 10).Address)

... I need to arrange to fit in other sheet, but this will be easy.

So what code do ... it went through selection of cells with formulas, read a range reference(s) (address(es)) in a formula, select this/these range(s) of cells and paste it on new location.

Check attached sample file ... sample.xlsm


Once again, thx for all your help and support! ... cheers, Marko

Originally Posted by StephenR

... I was a bit slow on the uptake ...

I don't think it was that, I think it was problem that I didn't know how to explain it well ... ... thx again!