CODEWITHSUNDEEP
excelvbareferenceformula
Ilai Segev
Ilai Segev

Reputation: 27

Finding a reference to a cell in an excel formula

Is there a formula or a macro that can find a reference to a cell in another cell? For example:

   A  |  B  |   C
------------------
1| 1  |  2  | =A1+B1

is there a formula that takes a C1 as input and returns =1+2?

Upvotes: 0

Views: 307

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

You may try this User Defined Function to convert the cell references used in the formula to their values as per your requirement.

Place the following Function on a Standard Module like Module1.

Function CellRefereceToValue(ByVal rng As Range) As String
Dim re As Object, Matches As Object, Match As Object
Dim str As String
Set re = CreateObject("VBScript.RegExp")
With re
    .Global = True
    .Pattern = "[A-Z]+\d+"
End With

str = rng.Formula

If re.test(str) Then
    Set Matches = re.Execute(str)
    For Each Match In Matches
        str = Replace(str, Match, Range(Match).Value)
    Next Match
End If
CellRefereceToValue = str
End Function

And then assuming your formula is in C2 as per the following image, use this Function on the Sheet like below...

=CellRefereceToValue(C2)

In the following image, Excel formulas are placed in column C and formula text in column D to show what formula is used in column C and then in column E, you can see the UDF replacing all the cell references used in the formulas in column C with their corresponding values.

enter image description here

Upvotes: 1

Related Questions