Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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; 2011-04-08 at 11:11. Reason: Added formulas to use 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
  •