Range issue in Worksheet_Calculate

[awriter]

I have been able to get the Worksheet_Calculate object to work for a single cell that has been defined as the range in Column B but I want it to work for a whole column. When I try, I get the message: "Object defined error"

What I am trying to do is when there is a Calculation that changes a cell in column B the value in column D is copy and pasted into column E (all of which are in the same row). There is an Error in the line Adam1 = Range("B" & ThisRow).Value

I am using XP SP2 with Excel 2003 SP2
Here is what I have:

Private Sub Worksheet_Calculate()
Adam1 = Range("B" & ThisRow).Value
Range("D" & ThisRow).Select
Selection.Copy
Range("E" & ThisRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Thank you in advance,
Adam

 

[Andrew Poulsom]

My code contains:

Application.EnableEvents = False

which disables the Change event. That was deliberate, to avoid having the Calculate event fire while my code was doing its stuff.

Try removing it and see what happens. If things don't work as you want we will have to incorporate that code into the Calculate event (when it changes column F).

 

[awriter]

I saw that and I did remove it and it did not work.
I get an error in the line:

If Target.Value <> 0 Then

Once again thanks for your help

 

[Andrew Poulsom]

I see that your Change event procedure is checking column F. That's what I used as the spare column in the Calculate event procedure. You will need to change that so that it uses a column that you are not already using.

When you have done that, post back with any problems, including the updated code.

 

[awriter]

I was just finishing writing a long message explaining how I was lost and I don't see the point of what you were trying to do with Columns D & E (infact the first go around i did not think you were doing anything with them), and then I think "I got it".

If I just put =now() in column D and in some other cell put =D - E I have my Timer!!!!!!!!

I can delete all of my code I have been working on and trying so desperately to incorporate into your code.

I will have to wait till tomorrow to verify that it works with the =RTD that I am using because I have to go for the day. But I am hopeful!!!!

I am going to need one more thing, and that is how to change the Column references? Column B and F are obvious, but where in the code would I change the location of columns D & E?

 

[awriter]

Well I tried it out today and it worked, sort of. Unfortunately there are still a couple of other issues. The major issue is that the CPU usage is maxed most of the time. I assume this is because I am looking at over 400 cells that are changing on average every 10 seconds which amounts to a lot of cut and pasting.
Andrew, I came across some other code and I wondered if this could be more efficient.

Private Sub Worksheet_Calculate()
If Intersect(UsedRange, [M:M]) Is Nothing Then Exit Sub

For Each cel In Intersect(UsedRange, [M:M]).Rows
r = cel.Row
If (Cells(r, "N") = "") Then
If (Cells(r, "M")) <> 0 Then
Cells(r, "N") = Now
End If
End If
Next
End Sub


The code works well. When there is an update in column M the time is stamped in column N. The only problem with this code is that when there is another update (as there would be about every 10 seconds in my case) the time stamp does not update again.
I have tried to remedy the situation by removing the line:
If (Cells(r, "N") = "") Then
and the corresponding End If but the result is that any time there is an update ALL timestamps update to the current time (not just the row in which the update took place).

Any other ideas???
Adam