Paste Special - Formulas - Add

[Guest]

I cannot get this function to work in the current release of Excel 2007 where
I am pasting a formula to a cell that already has a value in it. Excel just
pastes the values rather than the formulas and adds the value of the formula
to exisiting values.

I use this feature in a large number of our business spreadsheets.

Is anyone else aware of this problem and or a fix?

Thanks

 

[Roger Govier]

Hi

I have never ever used Paste Special>Formulas and Add together in any
version of Excel I have used from XL97 though to XL2007.
Somehow, I would have expected it to do exactly what you now describe.
However, I can see that in all previous versions it shows the original
cell value + the formula being copied with the result being the
combination of the two.

In XL2007, the behaviour is entirely different and it does, as you say,
evaluate the formula being copied and adds its result to the previous
value in the cell, and then contains just the final result.

I can see no way in XL2007 to amend this behaviour.
The only solution I can think of would be to use a simple macro like the
following.
I allocated it to a shortcut of Control+Q
The procedure then is to select the source cell with formula, Ctrl+C to
copy, move to destination cell and use Ctrl+Q

Sub PasteFormulaAndAdd()

Dim previous As Double
previous = ActiveCell.Value
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell = ActiveCell.Formula & "+" & previous
Application.CutCopyMode = False
End Sub

Maybe others will have a much better solution.