Results 1 to 2 of 2

20110408, 08:54 #1
 Join Date
 Feb 2003
 Location
 Warwick, Warwickshire, England
 Posts
 189
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20110408, 10:08 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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
Last edited by sdckapr; 20110408 at 10:11. Reason: Added formulas to use function

The Following User Says Thank You to sdckapr For This Useful Post:
RetiredGeek (20110408)