How to copy a formula or value into another formula

Question

I want to know how to copy a formula into another, while keeping the original values. For instance, I have in range A1:A5 five values (a mix of constants and/or formulas) and I need (in that same range – not in a new column) to include function =Round("original value or formula",2), (or any other formula; this is just an example)

For example:

A1 original value 50.64635
A2 original formula =3*F1 (which may display as 54.25875 if F1 contains 18.08625)
A3 original formula =SQRT(2) (which will display as 1.4142136)

Result I want is:

A1 new content (formula) =round(50.64635,2)
A2 new content (formula) =round(3*F1,2)
A3 new content (formula) =round(SQRT(2),2)

etc. Note that I do not want A1 to be changed to 50.65, and I do not want A2 to be changed to 54.26 or even =ROUND(54.25875,2), etc. I know how to do similar things with simple operations (copy/paste special – Formula – add, substract, multiply, divide) and keep original values or formulas. But I don't know how to wrap a function around an existing value or otherwise build a formula, using an existing formula as a component.

The other idea would be to play converting my original values/formulas to text, use find/replace function, concatenate, or some weird mix and I would come to result I want, but I would like to know if there is some shorter way ;)

score 0 · Accepted Answer · edited Mar 20 '17 at 10:16

Excel's "Find and Replace" capability doesn't seem to be powerful enough to do what you want. However, it's fairly easy in VBA.

First, make a copy of your file, just in case something goes wrong.

Then create the following VBA subroutine:

Sub Arom79Sub()

    On Error Resume Next
    For Each c In Range("A1:A5")
        If c.HasFormula And Left(c.Formula, 1) = "=" Then
            temp = Right(c.Formula, Len(c.Formula) - 1)
        Else
            temp = c.Value
        End If
        If temp <> "" Then
            c.Formula = "=ROUND(" & temp & ", 2)"
        End If
    Next c
    On Error GoTo 0

End Sub

See How do I add VBA in MS Office? for general information on how to use VBA in Excel and the other Microsoft Office applications. Arom79Sub is just an arbitrary subroutine name; use whatever name you want.

On Error Resume Next says, if an error occurs, just go to the next statement and keep on running. (For example, if a cell contains a constant value that is not a number (i.e., is a string/text value), the above code will cause an error.)
For Each c ... Next c is a loop that looks at every cell in the range A1:A5, setting c to refer to each cell in sequence.
c.HasFormula is a Boolean that tells whether cell c contains a formula. Then we also check to see whether the first character of the "formula" is =. This is probably redundant. If it's definitely a formula, set temp to the actual formula; i.e., everything to the right of the = (e.g., 3*F1, in your example for A2).
Otherwise, set temp to the value in the cell (e.g., 50.64635, in your example for A1).
If the value (or formula) is non-blank, set the cell's formula to be
=ROUND(previous_contents, 2).
Do On Error GoTo 0 to restore the normal error handling.

Then run the subroutine.

Obviously you can change the range or the new formula.

How to copy a formula or value into another formula

1 Answers1