none
Obtain row and column numbers from cell address formula RRS feed

  • Question

  • I am using Excel 2010 running in Windows 10.

    My problem is:-

    I have a workbook with 2 worksheets. The user selects a cell on Sheet2 then clicks “=”, then selects Sheet1 and selects a cell, then clicks “Enter”. So we have a selected (active) cell on Sheet2 that calls up a cell on Sheet1.

    I need a macro that returns the absolute row and column numbers of the selected cell on Sheet1 from the Sheet2 cell formula. I have been trying to write a macro using ActiveCell.FormulaR1C1, hoping to extract the row and column numbers from the formula using string functions. Unfortunately, the result for the formula has relative references, e.g. “=Sheet1!R[-2]C[-3]”. I cannot find a way to force the macro to return the cell address with absolute references e.g. “=Sheet1!R5C4”.

    Wednesday, May 3, 2017 9:59 AM

All replies

  • I deleted my previous post because I entered the initial formula as absolute and then realized that you want to convert the relative formula to absolute and return in R1C1 style.

    The following example converts the formula in the cell to absolute and saves the formula in R1C1 style to a variable and then extracts the row and column numbers using string and split functions.

    Sub Macro2()
       
        Dim strFormulaR1C1 As String
        Dim rng As Range
        Dim arrSplit    'For array for the Split function
       
        Set rng = Worksheets("Sheet2").Range("D3")
       
        'Convert formula to absolute in R1C1 format
        strFormulaR1C1 = Application.ConvertFormula(rng.Formula, xlA1, xlR1C1, xlAbsolute)
       
        Debug.Print strFormulaR1C1
       
        'Remove workbook and sheet from formula
        strFormulaR1C1 = Mid(strFormulaR1C1, InStr(1, strFormulaR1C1, "!") + 1)
       
        arrSplit = Split(strFormulaR1C1, "C")       'Split at "C" (removes the "C" during split)
       
        arrSplit(0) = Replace(arrSplit(0), "R", "") 'Remove "R" from first element of array
       
        Debug.Print "Row = " & arrSplit(0)     'Row number
        Debug.Print "Col = " & arrSplit(1)     'Column number

    End Sub


    Regards, OssieMac


    • Edited by OssieMac Wednesday, May 3, 2017 8:35 PM Edited code
    Wednesday, May 3, 2017 12:01 PM
  • Hi Leigho,
     
    Have a look at the application.ConvertFormula function. It should do what you
    need.
     
     
    Wednesday, May 3, 2017 2:40 PM
  • Hi Leigho,
     
    Have a look at the application.ConvertFormula function. It should do what you
    need.
     
     
    Wednesday, May 3, 2017 7:44 PM
  • I edited my previous reply because there is no need to save the original relative formula because the converted formula can be saved directly into a variable.

    This reply because you do not get an email advice when posts are edited and therefore may not see the edited code.


    Regards, OssieMac

    Wednesday, May 3, 2017 8:39 PM
  • hoping to extract the row and column numbers from the formula

    Yet another simplified method for extracting the row and column from the simple linked cell formula.

    Sub Macro4()
       
        Dim rng As Range
        Dim r As Long
        Dim c As Long
       
        Set rng = Worksheets("Sheet2").Range("D3")
       
        'Under test it does not appear to be necessary to remove
        'the leading equals sign from the formula
        r = Range(rng.Formula).Row
        c = Range(rng.Formula).Column
       
        Debug.Print "Row = " &  r     'Row number
        Debug.Print "Col = " &  c     'Column number

    End Sub


    Regards, OssieMac

    Wednesday, May 3, 2017 11:48 PM