Error when doing a paste

[Backslider]

This is a really simple problem to reproduce, but I can't see what I am
doing wrong. I have the following two functions in my workbook:

In Module1
Function MySum(arg1 As Double, arg2 As Double) As Double
MySum = arg1 + arg2
End Function

In Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
addr = Target.Address
Sheet3.Range(addr).Formula = Target.Formula
If (Err.Number <> 0) Then
MsgBox Err.Number & " - " & Err.Description
End If
End Sub

If I type =MySum(a7, b7) into cell A2 of Sheet1, I end up with the same
formula in cell A2 of Sheet3. Perfect. If I copy that formula from A2
on Sheet1 into B2 on Sheet1, the Worksheet_Change function fails. The
line
Sheet3.Range(addr).Formula = Target.Formula
gives error 1004 - Application-defined or object-defined error. There
is no formula in Sheet3!B2. If I then "edit" the cell Sheet1!B2, but
don't actually change anything, the SheetChange function works.

I don't get it. If I type the formula into the cell, it works. But,
if I copy/paste something that results in the same formula, then it
fails. What is it about the copy/paste that would cause that line to
fail?

thanks,
Jaimie

 

[Doug Glancy]

Backslider,

I've spent half an hour looking at this. I can reproduce the problem (XL
03) but I can't figure out the cause.

I notice that if I drag the fill handle using the mouse, it works fine, but
when I copy and paste, it fails as you describe.

Good luck.

Doug

 

[Backslider]

Backslider,

I've spent half an hour looking at this. I can reproduce the problem (XL
03) but I can't figure out the cause.

I notice that if I drag the fill handle using the mouse, it works fine, but
when I copy and paste, it fails as you describe.

Good luck.

Doug,

Thanks for looking into it.

This is a simplified example of what happens in our commercial
application (using VSTO 2003), so the "drag" workaround isn't going to
work. I don't think it is feasible to tell the customers that they
can't copy/paste.

If I could come up with some kind of code workaround to get Excel out
of whatever state it is in that it doesn't let you put the UDF into a
cell, that would be viable.

Jaimie

 

[Doug Glancy]

Jaimie,

I wasn't suggesting it as a workaround, for the reasons you mentioned. I
just thought it was worth mentioning - especially since I had nothing else
to offer <g>.

Doug