Hi, let me explain...

I have a link from the web which is a share price. I would like to record
the last 3 price changes and then average of all three. ( price changes can
happen in seconds or minutes)

I believe I am looking to copy the content initially as a static
value(cell1), when it changes to copy it to another static cell (cell3),
when it changes again copy this to another static cell (cell3)... I hope
this is clear enough

I can work out the average...lol

The next stage would be average of another change, cell 2 + 3 + 4 but lets
get the initial problem first... :-)

TIA

Hi Maxibo,
Does this give you any ideas?

If you paste the following into the relevant worksheet module, then
when ever a value is entered into A1 it is copied into A2. Next time A1
has a value entered A2 is copied to A3 and A1 is copied to A2. Next
time A1 has a value entered A3 is copied to A4, A2 is copied to A3 and
A1 is copied to A2. The non-zero value in A4 indicates that three new
values have been entered into A1 so the average of A2:A4 is entered
into B1 and A2:A4 is cleared in preparation for the next three values
entered into A1.

Adapt this code to detect the changes in your linked cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
If Range("A4") < "" Then
Let Range("B1") = Application.Average(Range("A2:A4"))
Range("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

Hi Maxibo,
If you don't want to lose the average every time a new one is
calculated then try the following. The time that each average was
calculated is kept in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

Many thanks for the assistance, now the embarresing bit..

How do I get this into excel, sorry thought I knew excel... ;-)


"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,
If you don't want to lose the average every time a new one is
calculated then try the following. The time that each average was
calculated is kept in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

Hi Maxibo,
1. Copy the code.
2. Right click the tab of the worksheet that contains the Stock link
then select View Code
3. Paste the code into the blank module
4. If you see any red lines that means that a line break has broken a
single line of code and you will have to edit it back to a single line.

See how that goes. Let me know

Ken Johnson

Im trying...lol

Do I get this VB thing up in Tools, Macro and paste it in there ?

If so it looks cool and all this is intriging me. However unsure if it
matters but the target.offset row is all red in the code.

Once again thank you for assistance which I am sure you have a busy
schedule.


"Maxibo" wrote in message
...
Many thanks for the assistance, now the embarresing bit..

How do I get this into excel, sorry thought I knew excel... ;-)


"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,
If you don't want to lose the average every time a new one is
calculated then try the following. The time that each average was
calculated is kept in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

WOW Ken, you are the man

I have used Excel for a few years but nothing to this degree.

Seeing your solution WORKED ! and now has given me ideas to use excel
better.


"Maxibo" wrote in message
...
Im trying...lol

Do I get this VB thing up in Tools, Macro and paste it in there ?

If so it looks cool and all this is intriging me. However unsure if it
matters but the target.offset row is all red in the code.

Once again thank you for assistance which I am sure you have a busy
schedule.


"Maxibo" wrote in message
...
Many thanks for the assistance, now the embarresing bit..

How do I get this into excel, sorry thought I knew excel... ;-)


"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,
If you don't want to lose the average every time a new one is
calculated then try the following. The time that each average was
calculated is kept in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

Hi Maxibo,

This code has to go into a worksheet code module, and the quickest way
there is to rightclick the sheet tab of the sheet that has the link.
When you do the right click you should see a little popup, and on the
bottom of that popup you should see "View code".
After selecting "View code" you will be in the VBA editor, and you
should see a blank (white) area where you can paste the code. This is
the code module for the relevant sheet.

Edit any red lines to get rid of the line break that has been produced
by the Groups editor.

Try that and let me know, I'm not really busy, but I'm sure you are.

Ken Johnson