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

http://www.vbaexpress.com/forum/showthread.php?19661-Showing-Formula-with-values

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.