Worksheet_calc
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Worksheet_calc
 

Citizen_cow wrote:
Converting it to worksheet_calc with my limited knowledge is driving me
insane.


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

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

Worksheet_calc
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Worksheet_calc
 

"Bill Manville" a écrit dans le message de
...
Citizen_cow wrote:
Converting it to worksheet_calc with my limited knowledge is driving me
insane.


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

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

U might try to replace TARGET by ACTIVE_CELL
It's not the samething but it might achieve what u want

René.

Worksheet_calc
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Worksheet_calc
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Worksheet_calc
 

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.

citizen_cow wrote:

I tried doing it the easiest way I knew thinking I was going at it the
hard way.

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



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

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

Worksheet_calc
 

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