Re: Get info from changing cell to a static cell ??

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Bill,
I think I've got it!

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim iRowOffset As Integer
Range("A1").Offset(3, 0).Value = Range("A1").Offset(2, 0).Value 'A4
takes on A3 value
Range("A1").Offset(2, 0).Value = Range("A1").Offset(1, 0).Value 'A3
takes on A2 value
Range("A1").Offset(1, 0).Value = Range("A1").Offset(0, 0).Value 'A2
takes on A1 value
If Range("A4") <> "" Then
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old averages down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 3).Value = Cells(iRowOffset, 3).Value
Next iRowOffset
Let Range("B1") = Application.Average(Range("A2:A4"))
'Put time when average was calculated into C1
Let Range("C1") = Now
Range("A5").Clear
End If
Application.EnableEvents = True
End Sub

It wouldn't work till I got rid of the first loop, which was pretty
unnecessary and down right confusing.
Now, as soon as a new price comes in it is fed into A2 after all the
values below it are shifted down one row. If A2, A3 and A4 all have
values then the average is calculated and fed into B1, after all older
averages are shifted down one row, and similarly with the time that the
average was calculated.

There could still be a problem though Bill. The code is triggered by
anything that causes automatic calculation to occur, and that's just
about everything, even just typing then entering text on any sheet.
Switching to manual calc won't do cause then nothing happens. So, it
looks like the whole workbook has to be devoted to this one purpose, at
least during the period of time that you are receiving downloaded data.

Ken Johnson

.

Relevant Pages

  • Re: Get info from changing cell to a static cell ??
    ... > Private Sub Worksheet_Calculate ... > Dim iRowOffset As Integer ... > 'Shift old time values down 1 row ... > anything that causes automatic calculation to occur, ...
    (microsoft.public.excel.programming)
  • Re: Challenge with target output
    ... So far i got calculation for returned in (g31), ... Private Sub Worksheet_Calculate ... Private Sub Worksheet_Calculate(ByVal Target As Range) ...
    (microsoft.public.excel.worksheet.functions)
  • Re: [BUG,2.6.28,s390] Fails to boot in Hercules S/390 emulator
    ... the negative difference between them into clock->error. ... What we are actually doing is storing the _remainder_ in xtime. ... problem with the calculation of clock->error. ... The calculated new error is correct given the shift. ...
    (Linux-Kernel)
  • Re: Employee Time Sheet Question
    ... The problem with the calculation is that by NOT ... I would use two UNBOUND fields to capture the TIME IN and TIME OUT and then use code to set the values of the BOUND controls. ... You may also want to add a warning/confirmation message when the value of TIME OUT is earlier than TIME IN to confirm that the shift spanned midnight. ... but I'm not getting the right totals back. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Once only calculation - help please
    ... I don't know anything about DDE--but maybe you can tie into a calculation event: ... Private Sub Worksheet_Calculate ... Dim myCell As Range ... > charting application directly into an Excel worksheet. ...
    (microsoft.public.excel.misc)