Thread: Extract the columns and rows used for a sum()

1. Extract the columns and rows used for a sum()

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

2. 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 < 1 Or 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```

3. The Following User Says Thank You to sdckapr For This Useful Post:

RetiredGeek (2011-04-08)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•