VBA-xl: how 2 create objects = moving ranges? Help !

[4321orso]

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 (would need Excel 2007)

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

 

[SkipVought]

Hi,

What you need to do is write your criteria as pseudo code. It seems that you have this range of values AND an order of values or sequencer. When this condition is met, the sequencer value of the FIRST range value becomes the sequence value of the MAX range value (or maybe the MAX range sequence value minus one) and all the sequencers between incriment by minus one. I suggest using a sequencer, in order to avoid INSERT & DELETE.

Posting an small representative data sample would also help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[4321orso]

hi SkipVought,

I will try to clarify what pesudo code and sequencer mean (must be somewhere in the books I have).

Are you saying that you think that the solution I outlined (ie creating objects and then putting a progressive number on them) is a promising one ?

What do you recommend I study ? object creation syntax ?

Thank you for ur help

Best Frank

 

[SkipVought]

Forget about objects. Just focus on the control stucture.

If condition then
what happens if the condition is true
Else
what happens if the condition is false
End

Make sure that all your logic is correct. THEN we can focus on how to do it in Excel VBA.

Here's some simple sample pseudo code (hint: correct syntax is not an issue)

If (today is Tuesday) then
  If (Time is less than 12:00) then
     It is Tuesday morning
  Else
     It is Tuesday noon or after
  End
Else
  It is not Tuesday
end

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[4321orso]

that sounds more straightforward, I guess the object-way is clumsy in comparison. I'll study the structure bit of my books tonight. Thank you again SkipVought, u r very helpful ! F

 

[4321orso]

hi Skip, I am still working on this. Here is an example of what I am looking to achieve. I also wrote some code lines and will post them aferwards.

I have an excel spreadsheet and in column 3 I have a data series (time is in column 2, not shown). In Col 4 I have the difference between the value for the data at taht point and the maximum value from the start of the range up to that point.
When the value in col 4 goes beyond a certain value (eg -2) I want the program to stop calculating the differences, "take note" of the row number where the condition is satisfied (the row where number 6 is in the example below, that would be row 5 if the series starts in row 1).

col 3 col 4
8 0
9 0
8 -1
7 -2
6 -3
5 not calculated


After this I want the program to search for the row number where the maximum value is (that is row 2, where number 9 is) and store the info (eg could copy the info to a cell).

At this point I want the program to start over again but using row 2 as a starting point and checking for minimum values. It would look like this:

col 3 col 4
8 not calculated
9 0
8 0
7 0
6 0
5 0
6 1
7 2
8 3
9 nc


The program checks what the minimum value is from row 2 onward and calculates, in each cell in the NEW range, the difference between the last data and the minimum up to that point. When the difference goes beyond a certain level (eg 2) I want the program to stop calculating the difference and take note of the row number where the difference condition was satisfied. That occurred in the cell where number 8 is, ie row number 9. At this point the program should search for the minimum value over range(Cells(2;3),Cells(9,3)). The position of the minimum is where number 5 is, ie row 6.

At this point the process starts all over again. The program should set Cells(5,3) as the new starting point for the range it works on and start looking for a max value (like in the first cycle above).

The program then goes on and on, alternating cheching for max and min, until it reaches the last data available in the data series. At that point it stops.

All values for max, min, and the corresponding points in time, should be made available (like pasting them to a range).
I will post in a bit the code lines i put together so far. Frank

 

[4321orso]

here is some code lines I wrote. It's a sub that finds the address of the local maximum in a range (same for minimum).
You can see it below in a standalone form (does not connect to anything else yet and values and data type I put in are quite arbitrary).

_____________________________________________
Option Explicit
Option Base 1

Private Sub FindRowOfLocalMax()

Dim rngData As range
Dim avData(1 To 50000) As Variant
Dim avDates(1 To 50000) As Variant
Dim j As Integer
Dim iRowMax As Variant
Dim dbMaxVal As Double

' find row number for cell where u find value of maximum on local range
Set rngData = range("b1:c10")
dbMaxVal = Application.Max(rngData)


' fill an array with local maximum values and time
For j = 1 To 10

If Cells(j, 3).Value = dbMaxVal _
Then
avData(j) = Cells(j, 3).Value
avDates(j) = Cells(j, 2).Value
Cells(j, 7).Value = avData(j)
Cells(j, 6).Value = avDates(j)

' find the most recent row where the data value is = to local maximum
iRowMax = Application.Max(avDates)

' paste it to check value
Cells(10, 8).Value = iRowMax
Else

' fill the array with zeros when data value not equal to max
avData(j) = 0 = Cells(j, 3).Value
avDates(j) = 0 = Cells(j, 2).Value
End If
Next

End Sub
_____________________________________________________


I think I might use that iRowMax as the next starting point for the calculation.

In other words the program could get started first with a sub that works "For n=1 To lastRow" (eg lastRow = range("c65000").End(xlUp).Row), until the difference condition I described earlier is satisfied for the first time.

When the condition is satisfied the program could launch "Private Sub FindRowOfLocalMax()", the sub u see above.

After that sub is completed the program could run " For j = iRowMax To lastRow", but checking for a minimum.

Do you think it might work ?

One of the problems for me is that I have no idea how to alternate between checking for a max and checking for a min value.

thank u for any help u can provide. Frank

PS: I'll be travelling until monday with limited access to the net. Have a great weekend !

 

[SkipVought]

One of the problems for me is that I have no idea how to alternate between checking for a max and checking for a min value.

You could use a counter. Could be the row counter. Do a Mod 2 on the counter. The result will be alternating 0 and 1 as the counter incriments by 1. When the result is 0, check MAX. When the result is 1, check MIN.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}