Thanks Nishant...
Again code does not work when value in cell A1 is updated dynamically
through an external source. I create a DDE link and paste to cell A1
so prices are updated automatically..
On Jun 4, 9:24 am, Nishant Jain <nishantjai...@gmail.com> wrote:
> 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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
