extract row line from the formula

[andyfas]

Hello,

in a cell, I have this formula:

=SUM('sheet1'!$B158:$B161)

I would like to extract the first reference of the line (158 in this case, but it could change) and put it into a variable.

Thanks for your help.

[Static]

in VBA?

this is kinda off the top of my head....

Public Function GetStart(sRng As String) As Integer
Dim fml As String
Dim cLoc As Integer
Dim bLoc As Integer
 fml = Range(sRng).Formula
 cLoc = InStr(fml, ":")
 For x = cLoc - 1 To 1 Step -1
    If IsNumeric(Mid(fml, x, 1)) = False Then
        bLoc = x + 1
        Exit For
    End If
Next
GetStart = CInt(Mid(fml, bLoc, cLoc - bLoc))
 
End Function
 
Private Sub test()
    MsgBox GetStart("B6")
End Sub