Extract the columns and rows used for a sum()

Topic

New Reply

If I create a sum in a cell eg. =sum(f47:f55), is there any way to look at this cell and extract the start column, start row, end column and end row into cells?

In this example:
start column would be F
start row would be 47
end column would be F
end row would be 55

I’ve looked at various ways of cutting up the text but wondered if there was some calculation which may just extract thes items for me

I don’t mind if the results have to be in individual cells – just need to extract them somehow

Regards

John

Reply | Quote

Replies

What are your trying to accomplish? There may be easier ways?

I imagine to do what you want would require a user function to parse the formula. The problem is determining how generic the parser has to be. This may be generic enough…

If the formula is in A1, use:
=RangeParser(A1,1)
for Start column

=RangeParser(A1,2)
for Start row

=RangeParser(A1,3)
for end column

=RangeParser(A1,4)
for end row

Steve

Code:

Option Explicit
Function RangeParser(rng As Range, iType As Integer)
  'iType Values
  '1 = Start Column
  '2 = Start Row
  '3 = End Column
  '4 = End Row
 
  Dim sFormula As String
  Dim iColumn As Integer
  Dim sRange As String
  Dim iOpen As Integer
  Dim iSep As Integer
  Dim iClose As Integer
  If iType  4 Then
    RangeParser = CVErr(xlErrNum)
    Exit Function
  End If
  sFormula = rng.Formula
  iOpen = InStr(sFormula, "(")
  iSep = InStr(sFormula, ":")
  iClose = InStr(sFormula, ")")
  Select Case iType
    Case 1
      iColumn = Range(Mid(sFormula, iOpen + 1, iSep - iOpen - 1)).Column
      sRange = Cells(1, iColumn).Address(False, False)
      RangeParser = Left(sRange, Len(sRange) - 1)
    Case 2
      RangeParser = Range(Mid(sFormula, iOpen + 1, iSep - iOpen - 1)).Row
    Case 3
      iColumn = Range(Mid(sFormula, iSep + 1, iClose - iSep - 1)).Column
      sRange = Cells(1, iColumn).Address(False, False)
      RangeParser = Left(sRange, Len(sRange) - 1)
    Case 4
      RangeParser = Range(Mid(sFormula, iSep + 1, iClose - iSep - 1)).Row
  End Select
End Function
 

Reply | Quote