Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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é. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|