Convert formula's cell reference into value

[bugzzz_1st]

Dear Masters,

I need your help. I want to create an formula that will transform my cell reff into values.

Eg.

cell A1 = 2
cell A2 = 4

cell A3 = A1+A2

After running the code the result in cell A3 should look like : cell A3 "= 2+4"

I had made some searches unfortunately finding unuseful hints ( like macro for copy -> paste or code for transform one column in values plus an other one ... )


Many thanks,
Laurentiu

[Aussiebear]

Not sure I'm following your logic here. You say you "want to create a formula that will transform my cell reff into values".

Cell references are the column & row headers created by Microsoft. Whereas the values ( 2 & 4) are values created by a user. How did you wish to proceed with this?

[Paul_Hossler]

I read the OP as asking about a 'description' of how as formula was evaluated. Handy, but likely impossible

Example:

A1 = 2
A2 = 4
A3 = formula of =A1+A2

In A4 formula of =MaybeThis(A3)

Option Explicit
Function MaybeThis(r As Range) As String
    Dim sFormula As String
    
    sFormula = r.Formula
    
    'this is where it gets sticky
    'hardcoded for simple A3 = "=A1+A2" formula
    MaybeThis = "=" & Range("A1").Value & "+" & Range("A2").Value
End Function

How could you handle things like =SUM(A3:COUNTIF(B:B,D6))*L20/2 .... etc.?????


Paul

[bugzzz_1st]

I read the OP as asking about a 'description' of how as formula was evaluated. Handy, but likely impossible

Example:

A1 = 2
A2 = 4
A3 = formula of =A1+A2

In A4 formula of =MaybeThis(A3)

Option Explicit
Function MaybeThis(r As Range) As String
    Dim sFormula As String
    
    sFormula = r.Formula
    
    'this is where it gets sticky
    'hardcoded for simple A3 = "=A1+A2" formula
    MaybeThis = "=" & Range("A1").Value & "+" & Range("A2").Value
End Function

How could you handle things like =SUM(A3:COUNTIF(B:B,D6))*L20/2 .... etc.?????


Paul

Hi Paul,


Thanks for your post. I don't have any ranges (A1:A9) i have only simple links with simple operators like +-/*.

My mistake that i mentioned i want a formula. If it can be done somehow else, great !
I just need a code that will bring the value of a cell instead of the cell reference (like instead of cell reff A1 we should have "2" )

Cheers,
Laurentiu

[Ringhal]

Try this code, with the cell of the formula selected:

   Sub AppendApostrophe()    
    Dim c As Range
    For Each c In Selection
        If c.Value <> "" Then c.Value = "'" & c.Formula
    Next
   End Sub

[mikerickson]

Take a look at
http://www.vbaexpress.com/forum/show...la-with-values