How to capture cell value on update?

martygladin

New Member
Joined
Dec 6, 2018
Messages
5
Hey guys! Thank you for taking time to read my post. I am stuck:confused:. I have some code that captures a cell's value on a timer, but I want it to capture the value only when it updates. It is for a live data connection to my stock broker. I want to capture the "last price" and record it on "Sheet2" so that I can pull that information into a chart. Any help would be greatly appreciated! I pasted the code below.

Dim NextTime As Double

Sub RecordData()
Dim Interval As Double
Dim cel As Range, Capture As Range
Interval = 5 'Number of seconds between each recording of data
Set Capture = Worksheets("Sheet1").Range("B9") 'Capture this column of data
With Worksheets("Sheet2") 'Record the data on this worksheet
Set cel = .Range("A2") 'First timestamp goes here
Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0)
cel.Value = Now
cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Application.Transpose(Capture.Value)
End With

NextTime = Now + Interval / 86400
Application.OnTime NextTime, "RecordData"
End Sub

Sub StopRecordingData()
On Error Resume Next
Application.OnTime NextTime, "RecordData", , False
On Error GoTo 0
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hello,

You will probably need to use the Event Macro : Worksheet_Change(ByVal Target As Range)

HTH
 
Upvote 0
Ok so I put this together but it doesn't quite work either. Can anyone see whats wrong with it?

Dim oldValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldValue = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Set Capture = Worksheets("Sheet1").Range("B9") 'Capture this column of data
With Worksheets("Sheet2") 'Record the data on this worksheet
Set cel = .Range("A2") 'First timestamp goes here
Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0)
cel.Value = Now
cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Application.Transpose(Capture.Value)
End Sub
 
Upvote 0
Could you explain in plain English what you are after ...

What is the purpose of creating the variable ' oldvalue ' ... if you are not using it anywhere else ...
 
Upvote 0
Could you explain in plain English what you are after ...

What is the purpose of creating the variable ' oldvalue ' ... if you are not using it anywhere else ...


Yes. Lets say that I have a data feed of a stock quote coming into the sheet. What I want the macro to do is everytime the stock quote changes, the macro grabs the old value and logs it into a list.

It may help if I give an example: In cell A2 is Ticker Symbol "XYZ" and cell B2 $15, Then stock price changes to 15.50 then changes to 15.75, then 15.60, 15.20, and again and again...you get the idea.

I want the macro to record:
$15
$15.50
$15.75
$15.60
$15.20
...
...
...

The original code that I posted worked, but it was based on a timer. If the timer is short (like 5 seconds) then it would record duplicate info and mess up the chart that was being made from the logged prices. (It would mess it up by creating flat lines marking time change but no price change.) I don't care about time except knowing when the price changed. Thats why I have the time stamp line in the code. Does that make sense?

So to answer your question as to why I made the 'oldvalue' variable was a novice attempt to make it capture the last quoted price before it changed. I don't know how to take the variable to make it work. I hope this helps clear things up. I really do appreciate your time and thoughts!
 
Upvote 0
Hello,

Thanks for your very clear explanations ...

With a live feed ( my guess DDE ..) Worksheet_Change event will not function ...

You would need to test the Worksheet_Calculate event ...

Something along these lines ...

Code:
Private Sub Worksheet_Calculate()
Dim cel As Range
Static oldvalue
  
  If Range("B9").Value <> oldvalue Then
      oldvalue = Range("B9").Value
      
      ' Insert your code
      With Worksheets("Sheet2")
        Set cel = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
        cel.Value = Now
        cel.Offset(0, 1) = oldvalue
      End With
  End If
  
End Sub

Hope this will help
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top