PDA

View Full Version : Convert formula's cell reference into value

bugzzz_1st
08-14-2013, 07:57 AM
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
08-15-2013, 02:39 AM
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
08-15-2013, 06:45 AM
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
08-15-2013, 11:41 PM
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
08-16-2013, 03:38 AM
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
08-16-2013, 07:45 AM
Take a look at