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]

Where are you assigning a value to ThisRow?

 

[awriter]

I really don't know where, if, or how to assign a value to ThisRow. My VB programing skills are very poor at best. I just search the message boards and do a lot of cut and pasting until it works.

As I said before I could get the code to work for an individual cell:

Private Sub Worksheet_Calculate()
Adam1 = Range("B15").Value
Range("D15").Select
Selection.Copy
Range("E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

But now I need it to work for every ROW in Column B. Please Help.

BTW- Thanks so much for your quick response Andrew.

Adam

 

[Andrew Poulsom]

What formula does column B contain?

 

[awriter]

For simplisity sake I have column B equal to whatever is in column A. (Example: cell B15 contains =A15).
In cell A15 I would be recieving real time updates from a quote service using =RTD.

 

[Andrew Poulsom]

The problem is establishing which cell in column B changed as a result of the calculation. The Calculate event doesn't have a Target argument like the Change event does.

What range of cells in column B can change?

 

[awriter]

That makes some sence to me because I originally started out working with the Change event. What I am really trying to accomplish is to create a Timer when a cell is changed/calculated. I was able to accomplish this if the cell was changed but not for a calculation. My original post was titled "Worksheet_Calculation to create a Timer".
As for your above question "what range of cells in column B can change?" There will be about 400 cells in column B all referenceing a different column (in this case A) all changing at different times. I would like it to look at each row individually.

 

[awriter]

Can I use the Range.Row property to assign a value to ThisRow???
Check this out:
http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.range.row.aspx

 

[Andrew Poulsom]

Can I use the Range.Row property to assign a value to ThisRow???
Check this out:
http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.range.row.aspx

Not really because you don't know which cell has changed.

I think what you will have to do is use a spare column to contain the old values in column B. In the Calculation event loop around column B comparing the new value with the old value. If it's different copy D to E for that row. When done copy/paste special values from column B to the spare column to update the old values.

Something like this, which uses column F to contain the old values:

Private Sub Worksheet_Calculate()
    Dim Rng As Range
    Dim Cell As Range
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set Rng = Me.Range("B1:B" & Me.Cells(Me.Rows.Count, 2).End(xlUp).Row)
    For Each Cell In Rng
        If Cell.Value <> Me.Range("F" & Cell.Row).Value Then
            Cell.Cells(1, 4).Value = Cell.Cells(1, 3).Value
        End If
    Next Cell
    Rng.Copy
    Me.Range("F" & Rng.Row).PasteSpecial Paste:=xlValues
    Me.Cells(1, 1).Select
    Application.CutCopyMode = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[awriter]

Andrew you are the MAN!!! Unfortunately for some reason I am still unable to accomplish what I need to get done. Your code does EXACTLY what I want it to do and it should work with the additional code that I have but it is not. If you could please look this over and see if you can figure out why this is not working????

Private Sub Worksheet_Calculate()
Dim Rng As Range
Dim Cell As Range
Application.EnableEvents = False
Application.ScreenUpdating = False
Set Rng = Me.Range("B1:B" & Me.Cells(Me.Rows.Count, 2).End(xlUp).Row)
For Each Cell In Rng
If Cell.Value <> Me.Range("F" & Cell.Row).Value Then
Cell.Cells(1, 4).Value = Cell.Cells(1, 3).Value
End If
Next Cell
Rng.Copy
Me.Range("F" & Rng.Row).PasteSpecial Paste:=xlValues
Me.Cells(1, 1).Select
Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
ThisRow = Target.Row
If Target.Value <> 0 Then
Range("G" & ThisRow) = Now()
Else
Range("G" & ThisRow) = 0
End If
End If

End Sub



The Worksheet_Calculate portion of the code should look familiar because you wrote it. The second part was supposed to execute using the Worksheet_Change command.
I had tested the second part of the VB code by copying and pasting (and special pasting) value into column 6 and the code would execute fine (basically giving me a time stamp of when the change occured).

Now why when your code pastes the value into column 6 the Worksheet_Change code does not execute?????


Thanks again for all of your help,
Adam