You're right. technically, A1 doesn't change. the VALUE of A1 changes, but the formula used to calculate the value does not.
I'll have to look into it... 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
