Get info from changing cell to a static cell ??

[Ken Johnson]

Hi Maxibo,

A new problem!
When I was triggering the Private Sub Worksheet_Calculate() code from
sheet3 using a link to Sheet1!I21 I happened to notice it failed if I
re-entered the same value into into the cell on Sheet3. However, I only
recently learnt a trick that forces the sheet to re-calculate using the
RAND() worksheet function...

Type the following into cell A1 on Sheet1:

=RAND()*0 + $I$21


and the Private Sub Worksheet_Calculate() code will be triggered even
when the same value is entered into $I$21.


As you know, I can't fully test this myself because I don't know how to
link Excel with the web. If it's not that difficult could you give me
some clues as to how it is done?

Let me know how this all works out, I'm very curious (could end up in
the stock market myself)

Ken Johnson

 

[Maxibo]

Hi Ken, (btw I am Bill...lol)

This RAND seems cool and is working at home.

I would love to have the link to the web too. Unfortunately it is on the
boss pc. It is an installed program with some sort of ODBC link to excel so
I cant generate it at home.

I had to send the code to his email, remote connect to his pc and configure
his excel. He thinks I am cool putting in all this code... thanks for that
but have I dug a hole for my self?

You beat me to it but I got sidetracked as the boss pointed out that the
presently calculation is every 3 changes, he asked if it could be average of
1,2,3 then average of 2,3,4 then 3,4,5 and so on...lol

I was stuck for 2 hours trying to understand
For iRowOffset = 3 To 1 Step -1
Range("A1").Offset(iRowOffset, 0).Value = Range("A1").Offset(iRowOffset - 1,
0).Value
Next iRowOffset

I could follow irowoffset stepping 3 down to 1 but then first step of the
For (loop is the terminolgy I believe) would be (3,0) is content of (2,0),
does the step -1 reverse everything i.e:-

(3,0) is value of (2,0)
(2,0) is value of (1,0)
1,0 is value of 0,0

by putting step-1 it reverses it so:-

1,0 is value of 0,0
2,0 is value of 1,0
3,0 is value of 2,0 . which is what happens.

Sorry if its confusing

Bill

 

[Ken Johnson]

Hi Bill,
So as soon as a price comes down you want the average of the new price
and the previous two. Now let me have a think about it.
I am glad to hear the code is being triggered.
I'll get back to you.
Ken Johnson

 

[Ken Johnson]

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

 

[Maxibo]

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]

Hi Bill,
That loop was driving me crazy too.
With Email, I use my Gmail account and yes I do get a lot of spam, but
Gmail stores it separate from legitimate mail and it's really easy to
manage and get rid of.
That'll be nice if you do get a xmas bonus, glad I could help!
I trust and hope that the problem of the code being triggered by just
about any activity in that workbook is manageable. BTW if it ever stops
working the first thing you should check out is - has
Tools>Options>Calculation been set to Manual? If so then change it back
to Automatic.
Ken Johnson