VBA Excel: Looping Subs. help !

[orsodani]

Hi everybody
I have've been asked to do something but I can get only to a point and I am now stuck. Please help.

I have written a Sub in VBA for Excel 2003 (see below) in which the top cell of a range (WorkRange) is initially set at Cells(1,2), where there is the first available price for a price series. Prices stream into the spreadsheet (streaming is provided independently from the Sub I wrote).
For each new price that streams in the Sub calculates the max price (called: MaxVal) and a min value (MinVal) for the price series over the range:

WorkRange="Cells(1,2),Cells(ActiveSheet.Columns(2).SpecialCells(xlLastCell).Row,2)"

that is the range from (1,2) to the bottom non-empty cell in column 2. The Sub also pastes the values to dedicated columns. The WorkRange keeps extending down in Excel as prices stream in the spreadsheet.
The Sub also calculates the updated differences betwen the last price and the MaxVal and MinVal calculated on the updated WorkRange (such differences I called diffM and diffI respectively).

My question is: how do I reset the top cell of the WorkRange when the variables diffM and diffI go beyond certain values ?

In other words if diffI=(LastPrice-MinVal)>(z*LastPrice) I want to reset the top cell of the WorkRange (or set a new WorkRange=WorkRange1) so that the top cell of the WorkRange is no longer Cells(1,2) but the cell where the MinVal is. I also want to keep unchanged all the output I got up to that point.

In other words. Say that the > condition occurred in cell (k,2) and that the MinVal (calculated over Range((1,2):(k,2)) is in cell (j,2), where j z then put value 1 in dedicated column, otherwise 0
If diffI > z * LastPrice Then Cells(LastRow, 8).Value = 1 Else Cells(LastRow, 8) = 0

' if (price - max) < -z then put value 1 in dedicated column, otherwise 0
If diffM < -z * LastPrice Then Cells(LastRow, 7).Value = 1 Else Cells(LastRow, 7) = 0

' send z*LastPrice in dedicated column
za = z * LastPrice
Cells(LastRow, 10).Value = za

End Sub
---------------------------------------------


I guess that the Sub can be made to Loop in one way or the other but I do not know how to do that and have not much time to search for solutions. Anybody can help ?

Thank you in advance
Frank