Combine Formulas into one cell

22 February 2006 by Ross McLean

Here is some simple code to combine a selction of formulas into one cell. I have not tested it very much so it's not guaranteed. It will also add a few extra (), than might be needed.

PLAIN TEXT

VBA:

Sub CombineFormulas()
Dim objCell As Object
Dim sTemp As String
Dim sOperator As String
Dim rOutPut As range
On Error GoTo errorhandel
sOperator = InputBox("Operator to use?", "CombineFormulas")
For Each cell In Selection
If Len(cell.Cells.Formula)> 1 Then
''get the formula, adds a "(" , remove the "=", add the closing ")", and puts a "+" on the end
sTemp = sTemp & "(" & Right(cell.Cells.Formula, (Len(cell.Cells.Formula) - 1)) & ")" & sOperator
Else
End If
Next
''Puts an "=" at the start, then gets rid of the last "+",
sTemp = "=" & Left(sTemp, Len(sTemp) - 1)
Set rOutPut = Application.InputBox("Put In Cell", Type:=8)
ActiveSheet.range(rOutPut.Address).Formula = sTemp
Exit Sub
errorhandel:
MsgBox "Error: " & vbNewLine _
& Err.Description & vbNewLine _
& Err.Number, _
vbOKOnly, "CombineFormulas"
End Sub