Hi guys,

I am writing (trying to write) a program in VBA for Excel 2003 and I got stuck, I need your help, please.

I have an excel range where the upmost cell is initially fixed and the downmost cell moves down as more data arrive.
In other words I have something like WorkRange = Range((1,1), (LastRow,1)).
In the range I have integers and LastValue is the value of the integer in cell (LastRow,1).
Over this range I calculate the maximum value (MaxVal) and the difference between LastValue and MaxVal.
So far it's (almost) easy, even for me.

Problem is, when the difference between LastValue and MaxVal satisfies a certain condition ( ie LastValue-MaxValue < z=constant) I need to move down the upmost cell in the WorkRange, so that it is set to coincide with the cell where the MaxVal is.
After that the program should me made to loop.
Normally the data series would be 40k rows long, but it could get 500k rows long I am afraid.

IN THEORY I have an idea of how I can solve this problem but I do not know how to do it practically with VBA.

I thought that maybe I could define the starting cell of the WorkRange as a Cell-object.
For example I could call it StartCell1. The WorkRange would then be:
WorkRange=Range(StartCell1,(LastRow,1)).
StartCell1 would be the first cell where I have data.

When condition ( LastValue-MaxValue < z) is satisfied I could create a Cell-object called LastCell1 that would be the first cell where the condition is satisfied.
I would also create a Cell-object called MaxVal1, that would be the cell where you find the maximum value in the range up to that point.
Finally I could also create a Range-object called WorkRange1=Range(StartCell1,LastCell1).

Done that I could create a new Cell-object, call it StartCell2 , and set it to have the same properties of Cell-object MaxVal1.
At that point the WorkRange would be WorkRange=Range(StartCell2,(LastRow,1)) and I could loop the program repeating the above.

Does it make sense ?
The problem is that I have no idea on how practically do this.
I am surfing through books and the web but did not find a solution so far. Can't see the shore either .... Anybody can please help ?

Thank you in advance
Frank


PS: I posted also yesterday but no replies. I hope I made my question clearer ...