Paste Special Formula Multiply feature doesn't work anymore

[theleo]

Has anyone noticed that the "Paste Special -> Formula -> multiply" feature
does not work and it works instead the same as "Paste Special -> Values ->
multiply"?

In Excel in 2003 I was able to copy a formula like '=$A$1' then "Paste
Special -> Formula -> multiply" onto a second cell (call it A2=$Z$1) and get
a new formula in A2 = $Z$1*$A$1.
Example:
Z1 = 5
A1 = $Z$1
A2 = 100

Copy cell A1 & click cell A2

In Excel 2003, "Paste Special -> Formula -> multiply" would result in cell
A2 becoming =100*$Z$1

In Excel 2007, "Paste Special -> Formula -> multiply" is resulting in cell
A2 becoming =500 (inherent calc being 100*5)

This suggests that Excel 2007 has a bug in feature "Paste Special -> Formula
-> multiply" because it works instead as Excel 2007, "Paste Special -> Values
-> multiply"

Has anyone found a solution from Microsoft. I can create a macro but I use
to use that feature a lot and don't want to create a macro every time. Help
will be appreciated. Thanks.

 

[Shane Devenshire]

Hi,

This is a known issue, and pretty annoying for some of us. Other than
writing VBA code to do it I don't know if there is a workaround.

Here is a little code you would attach to a VBA user form
(the rngFrom and rngTo are the name of two refEdit controls.

Private Sub btnOK_Click()
Dim X
X = Mid(Range(Me.rngFrom).Formula, 2, 10)
Range(Me.rngTo) = Range(Me.rngTo).Formula & "*" & X
Unload Me
End Sub

Private Sub btnCancel_Click()
Unload Me
End Sub

You also need to display the form with code like this:

Sub PasteSpecialMultiply()
frmPasteSpecialM.Show
End Sub