B
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
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