Hi guys,
I am writing (trying to write) a program in VBA (Excel 2003) and I got stuck. I need your help please.
HERE IS A DESCRIPTION OF WHAT I WANT TO DO
I have an excel range where the upmost cell is initially fixed and the downmost cell moves down as more data arrive.
I have something like WorkRange = Range((1,1), (LastRow.Row,1)).
That range contains integers. I call LastValue.value the value of the integer in cell (LastRow.Row,1).
Over this range I calculate the maximum value (MaxVal.Value) and the difference between LastValue.Value and MaxVal.Value.
So far it's (almost) easy, even for me.
The problem is, when the difference between LastValue and MaxVal satisfies a certain condition ( ie LastValue-MaxValue<z; 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.
POSSIBLE SOLUTION ?
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 upmost cell of the WorkRange as a Cell-object.
For example I could call it StartCell1. The WorkRange would then be:
WorkRange=Range(StartCell1,(LastRow.Row,1)).
StartCell1 would be the first cell where I have data.
When condition ( LastValue.Value-MaxValue.Value<z) is satisfied I could write the program to 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.Value,1)) and I could loop the program repeating the above.
Does this make sense ?
HOW YOU CAN HELP ME
I think one of my problems is that I am so inexperienced with programming syntax that it is very difficult for me even to know where to look in books and what keywords/commands to look for. I have 3 manuals sitting in front of me: 3700 pages in total, a bit depressing
You can help me in many ways. Please answer some of my questions, if you do not mind:
0) am I on the right forum ? If not, what forum should I post to ?
1) does the "strategy" I outlined above make sense or should I use another approach ?
2) where should I look in a manual to implement my solution/your solution ?
Any key word I should look for ?
If you can scribble down some of code lines in your reply that would help.
Probably my problem/questions are naive but can anybody please help ?
Thank you in advance
Frank
I am writing (trying to write) a program in VBA (Excel 2003) and I got stuck. I need your help please.
HERE IS A DESCRIPTION OF WHAT I WANT TO DO
I have an excel range where the upmost cell is initially fixed and the downmost cell moves down as more data arrive.
I have something like WorkRange = Range((1,1), (LastRow.Row,1)).
That range contains integers. I call LastValue.value the value of the integer in cell (LastRow.Row,1).
Over this range I calculate the maximum value (MaxVal.Value) and the difference between LastValue.Value and MaxVal.Value.
So far it's (almost) easy, even for me.
The problem is, when the difference between LastValue and MaxVal satisfies a certain condition ( ie LastValue-MaxValue<z; 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.
POSSIBLE SOLUTION ?
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 upmost cell of the WorkRange as a Cell-object.
For example I could call it StartCell1. The WorkRange would then be:
WorkRange=Range(StartCell1,(LastRow.Row,1)).
StartCell1 would be the first cell where I have data.
When condition ( LastValue.Value-MaxValue.Value<z) is satisfied I could write the program to 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.Value,1)) and I could loop the program repeating the above.
Does this make sense ?
HOW YOU CAN HELP ME
I think one of my problems is that I am so inexperienced with programming syntax that it is very difficult for me even to know where to look in books and what keywords/commands to look for. I have 3 manuals sitting in front of me: 3700 pages in total, a bit depressing
You can help me in many ways. Please answer some of my questions, if you do not mind:
0) am I on the right forum ? If not, what forum should I post to ?
1) does the "strategy" I outlined above make sense or should I use another approach ?
2) where should I look in a manual to implement my solution/your solution ?
Any key word I should look for ?
If you can scribble down some of code lines in your reply that would help.
Probably my problem/questions are naive but can anybody please help ?
Thank you in advance
Frank
