Try the macro
do the following and then copy the code below in a module
cell E1 = '00:00:01
cell E2 = validation list with 2 option "active, inactive"
Sub check_prices()
'created by Nishant Jain (nishantjai...@gmail.com)
Application.OnTime Now() + TimeValue(Range("E1").Value), "run_code"
End Sub
Sub run_code()
If Range("E2").Value = "Active" Then
If Range("B1").Value <> Range("A1").Value Then
Range("B1:B19").Select
Selection.Copy
Range("B2").Select
ActiveCell.PasteSpecial xlPasteValues
Range("B1").Value = Range("A1").Value
Range("A1").Select
End If
Application.StatusBar = "Last run at " & Now()
Else
Application.StatusBar = False
End If
Call check_prices
End Sub
On Jun 3, 11:10 pm, TAlgo <trading.a...@gmail.com> wrote:
> i need to check on date and time...
>
> On Jun 3, 1:41 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>
>
> > I think I may have something...
> > but there's a new wrinkle...
> > I know A1 is being "updated" from an external source.
> > let's say it updates every minute...
> > but what if the value hasn't CHANGED since the last update?
> > let's say for the last 10 minutes, the values were:
>
> > 1,2,5,5,5,6,7,5,5,3
>
> > Would it be acceptable to show the last 20 DIFFERENT values?
> > as in: 1,2,5,6,7,5,3 ??
>
> > If so, I think we can use the "Calculate" event to trigger
> > the update.
>
> > Private Sub Worksheet_Calculate()
> > Dim x
> > If (ActiveSheet.Cells(1, 2) <> ActiveSheet.Cells(1, 1)) Then
> > For x = 20 To 2 Step -1
> > ActiveSheet.Cells(x, 2) = ActiveSheet.Cells(x - 1, 2)
> > Next x
> > ActiveSheet.Cells(1, 2) = ActiveSheet.Cells(1, 1)
> > End If
> > End Sub
>
> > now, if you really want to keep the duplicates
> > (just to show that the values haven't changed for x number of cycles)
> > then... is it possible for your DDE update to also update a cell with
> > the TIME of the last update?
> > then, do the same thing but also record the date/time of the data record?
>
> > ??
>
> > Paul
>
> > ________________________________
> > From: TAlgo <trading.a...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Wednesday, June 3, 2009 12:50:00 PM
> > Subject: $$Excel-Macros$$ Re: Excel Macro - copying cell value as it changes
>
> > Paul, it works but my value in cell A1 is derived from external
> > sources via DDE. you think worksheet event does not recognize cell
> > value if its DDE?
>
> > On Jun 3, 12:32 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
>
> > > the simplest approach would be to use a
> > > worksheet Change Event like:
>
> > > Option Explicit
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim X
> > > If (Target.Address = "$A$1") Then
> > > For X = 20 To 2 Step -1
> > > ActiveSheet.Cells(X, 2) = ActiveSheet.Cells(X - 1, 2)
> > > Next X
> > > ActiveSheet.Cells(1, 2) = ActiveSheet.Cells(1, 1)
> > > End If
> > > End Sub
>
> > > hth
>
> > > Paul
>
> > > ________________________________
> > > From: TAlgo <trading.a...@gmail.com>
> > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > Sent: Wednesday, June 3, 2009 10:47:37 AM
> > > Subject: $$Excel-Macros$$ Excel Macro - copying cell value as it changes
>
> > > Hello All,
>
> > > I'm stuck on this one. Any help will be greatly appreciated.
>
> > > Let me see if I get this correct. I' have a cell - A1 which receives
> > > 'LAST Traded Price ' of a stock through DDE so cell A1 keeps changing
> > > dynamically during trading session.
>
> > > I need a macro which will copy value from A1 and place in B1
>
> > > then when A1 changes it copies to B1 and earlier B1 value moves to B2
> > > and so on......but i want this process to be limited up to B 20 .so no
> > > value in B21....
>
> > > hope its clear otherwise just let me know if you have questions
>
> > > Thanks and regards,- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
