Worksheet_calc

  • Thread starter Thread starter citizen_cow
  • Start date Start date
C

citizen_cow

Hi all. WIth the help of the VisualBasicForum I conquered this code a
Worksheet_change only to realize what I really needed wa
worksheet_calc.
What Im doing is each time the cell is updated it will write tha
update to sheet2 ("Output") as a new appended record. This worked GREA
with worksheet change but my ref cell is updated by a Paste_link.

Converting it to worksheet_calc with my limited knowledge is driving m
insane.
Any help?


Private Sub Worksheet_Calculate()
Dim total As Range
Dim r As Long
Set total = Worksheets("sheet1").Range("a1")
IF NOT INTERSECT(TOTAL, TARGET) IS NOTHING THE
With Worksheets("output")
r = .Range("A65536").End(xlUp).Offset(1, 0).Row
.Cells(r + 1, 1).Value = total.Value

End With
End If
End Sub

The Bold is where Im getting the error (OBJECT REQUIRED) and I assum
if I ever get past it the next line will stop me as well.

Thanks for the time
Cow
 
Citizen_cow said:
Converting it to worksheet_calc with my limited knowledge is driving me
insane.
Click to expand...

You can't tell what cells have been changed by calculate.
(Unless you keep a copy of the values in all the cells and compare them
all).
IF NOT INTERSECT(TOTAL, TARGET) IS NOTHING THEN
Click to expand...
The error is because Worksheet_Calculate does not have a Target argument
telling you what has been changed (unlike Worksheet_Change).

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
How I had it set up with Change was when the cell changed (A1) it put
copy of the change on the ouput sheet. The next change would appen
after the first on the ouput sheet and so on.
This is the function I need excpt since it is a coded cell I need it t
fire the copy paste with the worksheet calc function.
I thought it would be as simple as changing the command
 
Bill Manville said:
You can't tell what cells have been changed by calculate.
(Unless you keep a copy of the values in all the cells and compare them
all).

The error is because Worksheet_Calculate does not have a Target argument
telling you what has been changed (unlike Worksheet_Change).

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
Click to expand...
U might try to replace TARGET by ACTIVE_CELL
It's not the samething but it might achieve what u want

René.
 
Ive changed Target to Active cell or A1 or anything. I keep gettin
object errors. Im not so fluent in command language and am reading u
and in the help file but have noticed when you are trying to customiz
something it is really up to you to make all the commnads work hand i
hand.
Obviously Im having issues. :
 
I tried doing it the easiest way I knew thinking I was going at it th
hard way.

Private Sub Worksheet_Calculate()
Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
ACTIVESHEET.RANGE(\"A65536\").END(XLUP).OFFSET(1).PAST



End Sub
But I get a
"Object doesnt support this property method" on the bolded line
when Calc fires
 
How about:

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("a1").Copy _
Destination:=Worksheets("Sheet2") _
.Range("A65536").End(xlUp).Offset(1)
Application.EnableEvents = True
End Sub

But you really want this to do it each time the worksheet calculates?

Plop a:
msgbox "hi from worksheet calculate"
in the middle of the code. You'll be surprised at how many times it gets run.
 
Further to Dave Peterson's reply, if you want to only note this one
cell value (A1) if it has actually changed during a calculation pass,
you could use:

Private Sub Worksheet_Calculate()
If Me.Range("A1").Value<> Worksheets("Sheet2") _
.Range("A65536").End(xlUp).Value Then
Application.EnableEvents = False
Me.Range("a1").Copy _
Destination:=Worksheets("Sheet2") _
.Range("A65536").End(xlUp).Offset(1)
Application.EnableEvents = True
End If
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Back
Top