Range issue in Worksheet_Calculate

awriter

New Member
Joined
Sep 18, 2006
Messages
12
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

awriter

New Member
Joined
Sep 18, 2006
Messages
12
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
 

awriter

New Member
Joined
Sep 18, 2006
Messages
12

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Sep 18, 2006
Messages
12

ADVERTISEMENT

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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

Code:
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

New Member
Joined
Sep 18, 2006
Messages
12
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
 

Forum statistics

Threads
1,136,994
Messages
5,679,030
Members
419,800
Latest member
spvsr999

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top