Asked by:
Obtain row and column numbers from cell address formula
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”.
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 numberEnd Sub
Regards, OssieMac
 Edited by OssieMac Wednesday, May 3, 2017 8:35 PM Edited code



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

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 numberEnd Sub
Regards, OssieMac