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

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

Wow Ken, so simple when I see yours....lol

Using that average for A2:A4 was sweet,

I was trying to store values in variables and getting in a mess... glad you
got rid of that first loop, it was going round my head last night when
trying to sleep.

If you need any help with Small Business Server 2003, Networks, PC's just
ask. Anything else I might know a man who can...lol

It all worked manually, might get a xmas bonus monday AM... code is in boss
excel but the futures markets do not open at the weekend so nothing to test
till monday.

Thanks Again

p.s I see you are using your own email address in your posting?. I was
always advised not to or put something in it to break it up as a forum
spider will pick up your email and you will get loads of spam.


"Ken Johnson" <KenCJohnson@xxxxxxxxx> wrote in message
news:1134836307.309668.204170@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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: [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: 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: Round-Off Errors HP 48G
    ... Northing Easting ... the entire group of coordinates is "shifted" down to near the coordinate system origin for the purpose of the calculation. ... Now without the "shift", the answer is: ... You can see how the longer the list of high numbered coordinate values is, the larger a number \GSLIST will return, and with only 12 available digits you run out of digits pretty quickly. ...
    (comp.sys.hp48)
  • 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)