Is this possible?

[Todd Virlee]

Value1 Value2 Value3

Where the above are in cells A1, A2, and A3.

If a different cell has a formula =A1+A2, can we have a macro that would put
the translation in a cell below it, so it would look like ={Value1}+{Value2}?

 

[Tom Hutchins]

Try this variation of the code Gary''s Student sent. Should work for any
formula following the pattern of your example, with or without parentheses.

Select the cell containing the formula and run:

Sub document_it2()
Dim r As Range, v As String
Dim w As String, x
Dim y As String, z As String
Set r = ActiveCell
If InStr(r.Formula, "(") > 0 Then
v = Right(r.Formula, Len(r.Formula) - InStr(r.Formula, "("))
Else
v = Right(r.Formula, Len(r.Formula) - InStr(r.Formula, "="))
End If
If InStr(v, ")") > 0 Then
w = Left(v, InStr(v, ")") - 1)
Else
w = v
End If
x = Split(w, "+", -1)
y = "="
For n = LBound(x) To UBound(x)
y = y & Range(x(n)).Value & "+"
Next n
z = Left(y, Len(y) - 1)
r.Offset(1, 0).NumberFormat = "@"
r.Offset(1, 0).Value = z
End Sub

Hope this helps,

Hutch

 

[Todd Virlee]

That code does not work at all.

Try this variation of the code Gary''s Student sent. Should work for any
formula following the pattern of your example, with or without parentheses.

Select the cell containing the formula and run:

Sub document_it2()
Dim r As Range, v As String
Dim w As String, x
Dim y As String, z As String
Set r = ActiveCell
If InStr(r.Formula, "(") > 0 Then
v = Right(r.Formula, Len(r.Formula) - InStr(r.Formula, "("))
Else
v = Right(r.Formula, Len(r.Formula) - InStr(r.Formula, "="))
End If
If InStr(v, ")") > 0 Then
w = Left(v, InStr(v, ")") - 1)
Else
w = v
End If
x = Split(w, "+", -1)
y = "="
For n = LBound(x) To UBound(x)
y = y & Range(x(n)).Value & "+"
Next n
z = Left(y, Len(y) - 1)
r.Offset(1, 0).NumberFormat = "@"
r.Offset(1, 0).Value = z
End Sub

Hope this helps,

Hutch

 

[Tom Hutchins]

It works when I run it. I have uploaded a sample file with the macro and some
dummy data to:

http://freefilehosting.net/download/454ic

Download & open the sample file. Select any one of the cells highlighted in
yellow or green. They have formulas with/without parentheses. Then run the
macro.

Hutch