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.
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
Related posts: