In a nutshell: I am trying to get around a circular reference problem b
setting a storage cell's value equal to another cell's value via VBA.

I have a procedure written in the Worksheet_Change event so that whe
certain cells are updated the sum is copied to my storage cell. Thi
works beautifully when I directly edit the cells being summed.
However, these cells are normally calculated by other UDFs. If
overwrite the formula in one of these cells with a value, thi
procedure works correctly. Here is the problem: When I copy th
orignal formula over the value my sum cell's value is being read a
Empty by the code, even though it has a value in it. When it is Empty
it skips the assignment of the variable so my storage cell remains wha
it was before and doesn't switch to Empty as well.

I've put the variables on Watch and it only evaluates to Empty when
copy and paste my formula from another cell in the range to the Targe
cell. It also doesn't have a problem with regular copying and pastin
because I tried it in a simplified environment and it worked fine.

After running the code, the Sum cell does correctly calculate it
value. During the code its value is somehow Empty. I thought mayb
there is a timing issue with the Change event occuring prior to the su
cell being calculated? Can anyone offer insight?

The procedure code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A31,I31:AO31")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Dim a As Range
Dim x As Integer, y As Integer
Set a = Target
x = a.Column
y = a.row

'The storage cell --- The sum cell which evaluates t
Empty
Cells(y, 42).Value = Cells(y, 8).Value
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub

Scenario:
Cell H31 = sum(I31:AO31)-A31
Cell J31 = a complex user defined function
Cell K31 = 500
I copy cell J31 and paste into cell K31. The result is that th
storage cell is not updated

--
Etienn
-----------------------------------------------------------------------
Etienne's Profile: http://www.excelforum.com/member.php...fo&userid=3654
View this thread: http://www.excelforum.com/showthread.php?threadid=56298