Get info from changing cell to a static cell ??

M

Maxibo

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
 
K

Ken Johnson

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
 
K

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
 
M

Maxibo

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

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

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
 
M

Maxibo

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.
 
M

Maxibo

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.
 
K

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
 
K

Ken Johnson

Hi Maxibo,
Well done. Glad to help.
PS I noticed we are in the same time zone. Shouldn't you be asleep. I
know I should.
Ken Johnson
 
K

Ken Johnson

Hi Maxibo,
Call me a pillock. I guess I don't know how to interpret the times they
attach to the Groups posts. I'm in Sydney, Australia and it is now 1:50
am. I guess it's to do with Greewich Mean Time since your's is -0000.
I'd better stick to Excel

Ken Johnson
 
M

Maxibo

Hi Ken

Entering figures in manually to A1 works fine.

However, in the live environment I put a link to the share price in A1 and
nothing happened.

I put the website link somewhere else on the worksheet and put in A1 ' =cell
where share price is ' in this instance i21.

The first changed was entered in the next line but when the price changed
nothing happened.

It seems the A1 cell will only move on if the ' return / enter ' key is
pressed...lol

Wonder who will fix first..

Cheers

p.s assuming you are still awake.
 
K

Ken Johnson

Hi Maxibo,
That's the worst news I've had all day!
I'll look into it. The code is in a worksheet_change module so should
be triggered when any cell changes.

Ken Johnson
 
K

Ken Johnson

Hi Maxibo,
try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$I$21" Then
Exit Sub
End If
Application.EnableEvents = False
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Range("A1").Offset(iRowOffset, 0).Value = Range("A1").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
Application.EnableEvents = True
End Sub

Let me know who won!
 
K

Ken Johnson

Hi Maxibo,

Call me a pillock!
The fourth last line: Range ("A1").Clear is deleting the link to I21.
Just get rid of that line in the above code. I hope that does it.
Did I win?
 
M

Maxibo

You should goto sleep...lol

Nothing works...lol, try putting a value into i21

I was looking at same concept though $i$21..lol
 
K

Ken Johnson

Hi Maxibo,
Did you paste in the new code where I changed
"Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset -
1,0).Value" to " Range("A1").Offset(iRowOffset, 0).Value =
Range("A1").Offset(iRowOffset - 1,0).Value" and get rid of the fourth
last line that was deleting the link to I21 in A1?
It has to work.It works for me when I change the value in I21
Unfortunately I know nothing about linking up Excel with the web, so I
can't try out anything that way. I'll see my son after we've both had a
sleep. He might know something.
Ken Johnson
 
K

Ken Johnson

Hi Maxibo,
It's not looking good down here. I threw in another link in I21 linking
it with a cell on sheet3. I then arranged windows vertically to watch
what happened when I changed the cell on sheet3. Sheet1! I21 changed as
did Sheet1!A1 but the code was not triggered. So the sheet's changing
but it's not triggering the Worksheet_Change code.
I'm definitely going to bed now.
Ken Johnson
 
K

Ken Johnson

Hi Maxibo,
I may have hit on something!


Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Range("A1").Offset(iRowOffset, 0).Value = Range("A1").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
Application.EnableEvents = True
End Sub


This resides in the same module but is triggered everytime the sheet
does a calculation.
Delete or comment out the other Sub and paste the above code in place.


If you look at the top of the module on the right side you should see a
popup list that contains all of the events that a worksheet can use as
a code trigger. Calculate is the event we should have been using (I
think). Try this out with your pesky little web link.
There's also a FolowHyperlink Event which might be relevant. I'm not
sure what that means!
I still might win!

Ken Johnson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Get info from changing cell to static cell 1
Looping solver for recursive optimization 6
Static Web Query 1
VBA set thick borders around non empty cells in range 0
Saving Changes to Excel App 1
merging cells together but keeping all data from the cells 3
Newbie needs help with formula 7
Inheritance problem 5

Top