How to Loop?
in [Programming]
|
Prev: How to reference a cell into a Dialog Box ?
Next: Unable to open links from Excel in new tab - keeps opening new
From: Rob on 8 Mar 2010 02:29 Hi, With reference to the code below. I would like to loop this code at specific one minute time intervals two cells to the right. The idea is to have the loop running from 10am to 4pm. Assuming for example that cell i6 is the active cell at 10am and then at 10:01am k6 becomes the active cell. Sub CheckVolumeRise2() If Range("i6") < Range("k6") And Range("k6") < Range("m6") Then CriteriaReached.Show End If End Sub So at 10:01am the routine would be checking k6 < m6 <o6. Please will someone show me how to implement this. Thank you.
From: Bob Phillips on 8 Mar 2010 05:08
Private nTime As Double Public Sub StartJob() nTime = TimeValue("10:00:00") Application.OnTime nTime, "CheckVolumeRise2" End Sub Public Sub CheckVolumeRise2() Static rng As Range If rng Is Nothing Then Set rng = Range("I6") If rng.Value < rng.Offset(0, 2).Value And _ rng.Offset(0, 2).Value < rng.Offset(0, 4).Value Then CriteriaReached.Show End If If Now < TimeValue("16:00:00") Then nTime = nTime + TimeValue("00:01:00") Application.OnTime nTime, "CheckVolumeRise2" End If End Sub -- HTH Bob "Rob" <Rob(a)discussions.microsoft.com> wrote in message news:1DE5BA5A-64E4-4957-85D0-E86BD8BC6B61(a)microsoft.com... > Hi, > With reference to the code below. I would like to loop this code at > specific > one minute time intervals two cells to the right. The idea is to have the > loop running from 10am to 4pm. Assuming for example that cell i6 is the > active cell at 10am and then at 10:01am k6 becomes the active cell. > > Sub CheckVolumeRise2() > If Range("i6") < Range("k6") And Range("k6") < Range("m6") Then > CriteriaReached.Show > End If > End Sub > > So at 10:01am the routine would be checking k6 < m6 <o6. > > Please will someone show me how to implement this. > > Thank you. > |