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, --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
