VBA-Excel: how can i make objects of moving ranges ?

beartech · 09-24-2009, 07:51 AM

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 a couple 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

venkat1926 · 09-30-2009, 08:45 PM

an example would be more helpful
suppose the data is like this from A1 dwn

2
9
3
7
8

now first cell A1 is 2, maximum is 9 in A2,then last cell A5 is 8

what do you want to do????

beartech · 10-01-2009, 02:27 AM

hi Venkat, good idea
imagine u have an excel spreadsheet and in column 3 u have the data series. In Col 4 u have the difference between the last data and the maximum value from 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

(In col 2, not shown, there is time).
Then 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 the difference between the last data and the minimum up to that point in the NEW range. When the difference goes beyond a certain level (eg 2, where number 8 is, that would be row number 9) I want the program to stop calculating the difference, take note of the row number (row 9) and then search for the minimum value over range(Cells(2;3),Cells(9,3)). This would be where number 5 is, ie row 6.

At this point the process starts all over again. It sets Cells(5,3) as the new starting point for the range it works on and starts 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).
Thank you for ur help, Venkat1926. I will post in a bit the code lines i put together so far. Frank

beartech · 10-01-2009, 02:30 AM

Hi Venkat,

i tried to split the problem in modules.
The following one takes care of finding the local maximum in a range.
It is in a standalone form and does not connect to anything else so far.
I haven't optimised anything so far, it's all very rough so far..

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)

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)
iRowMax = Application.Max(avDates)
Cells(10, 8).Value = iRowMax
Else
avData(j) = 0 = Cells(j, 3).Value
avDates(j) = 0 = Cells(j, 2).Value
End If
Next

End Sub

beartech · 10-01-2009, 03:15 AM

Hi Venkat,

the only thing I wrote that works so far is 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).

_____________________________________________
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 I could write first a Sub that works "For n=1 To lastRow" (lastRow = range("c65000").End(xlUp).Row) to get the program started.

When a 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 again for ur support Venkat1926, I really appreciate it ! Best Frank

beartech · 10-01-2009, 03:22 AM

I'll be travelling until Sunday evening with little access to the net, will check in when i can, take care

venkat1926 · 10-02-2009, 12:19 AM

as you suggested problem solving is divided into modules

first module is filling up column d with cells value-max

your values in column c form c1 down (c1 is column heading)
this is basic data

h3
8
9
8
7
6
5
now run this macro and see what happens in column D
the macro is

Code:

Sub test()
Dim r As Range, c As Range, mx As Double, dif() As Double
Dim j As Integer, k As Integer, m As Integer
Worksheets("sheet1").Activate
Set r = Range(Range("C2"), Range("C2").End(xlDown))
mx = WorksheetFunction.Max(r)
MsgBox mx
j = WorksheetFunction.Count(r)
MsgBox j
ReDim dif(1 To j)

For k = 1 To j
dif(k) = r.Cells(k, 1) - mx
r.Cells(k, 2) = dif(k)
If dif(k) < -2 Then r.Cells(k, 2) = "NC"
Next k
'====================
m = WorksheetFunction.Match(mx, r, 0) + 1
MsgBox m
r.Cells(1, 3) = m
End Sub

the macro fills up the column d with the difference between column c value and the max and also in E2 the row of the maximum
the result (colum C D and E will be
col C colD col E
h3 h4 h5
8 -1 3
9 0
8 -1
7 -2
6 NC
5 NC

now you want to loop this action from c3 down, c4 down. etc.
iI presume the difference between c value and the NEW max will be filled in the same column D deleting D2. . in that case row of the maximum will be in E3. Is this logic correct

On Monday go through this and give your feedback.

09-24-2009, 07:51 AM	#1 (permalink)
beartech Registered User Join Date: Sep 2009 Posts: 5 OS: vista	VBA-Excel: how can i make objects of moving ranges ? 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 a couple 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

09-30-2009, 08:45 PM	#2 (permalink)
venkat1926 Registered User Join Date: Sep 2009 Posts: 13 OS: windows xp	Re: VBA-Excel: how can i make objects of moving ranges ? an example would be more helpful suppose the data is like this from A1 dwn 2 9 3 7 8 now first cell A1 is 2, maximum is 9 in A2,then last cell A5 is 8 what do you want to do????

10-01-2009, 02:27 AM	#3 (permalink)
beartech Registered User Join Date: Sep 2009 Posts: 5 OS: vista	Re: VBA-Excel: how can i make objects of moving ranges ? hi Venkat, good idea imagine u have an excel spreadsheet and in column 3 u have the data series. In Col 4 u have the difference between the last data and the maximum value from 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 (In col 2, not shown, there is time). Then 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 the difference between the last data and the minimum up to that point in the NEW range. When the difference goes beyond a certain level (eg 2, where number 8 is, that would be row number 9) I want the program to stop calculating the difference, take note of the row number (row 9) and then search for the minimum value over range(Cells(2;3),Cells(9,3)). This would be where number 5 is, ie row 6. At this point the process starts all over again. It sets Cells(5,3) as the new starting point for the range it works on and starts 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). Thank you for ur help, Venkat1926. I will post in a bit the code lines i put together so far. Frank

10-01-2009, 02:30 AM	#4 (permalink)
beartech Registered User Join Date: Sep 2009 Posts: 5 OS: vista	Re: VBA-Excel: how can i make objects of moving ranges ? Hi Venkat, i tried to split the problem in modules. The following one takes care of finding the local maximum in a range. It is in a standalone form and does not connect to anything else so far. I haven't optimised anything so far, it's all very rough so far.. 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) 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) iRowMax = Application.Max(avDates) Cells(10, 8).Value = iRowMax Else avData(j) = 0 = Cells(j, 3).Value avDates(j) = 0 = Cells(j, 2).Value End If Next End Sub

10-01-2009, 03:15 AM	#5 (permalink)
beartech Registered User Join Date: Sep 2009 Posts: 5 OS: vista	Re: VBA-Excel: how can i make objects of moving ranges ? Hi Venkat, the only thing I wrote that works so far is 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). _____________________________________________ 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 I could write first a Sub that works "For n=1 To lastRow" (lastRow = range("c65000").End(xlUp).Row) to get the program started. When a 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 again for ur support Venkat1926, I really appreciate it ! Best Frank

10-01-2009, 03:22 AM	#6 (permalink)
beartech Registered User Join Date: Sep 2009 Posts: 5 OS: vista	Re: VBA-Excel: how can i make objects of moving ranges ? I'll be travelling until Sunday evening with little access to the net, will check in when i can, take care

10-02-2009, 12:19 AM	#7 (permalink)
venkat1926 Registered User Join Date: Sep 2009 Posts: 13 OS: windows xp	Re: VBA-Excel: how can i make objects of moving ranges ? as you suggested problem solving is divided into modules first module is filling up column d with cells value-max your values in column c form c1 down (c1 is column heading) this is basic data h3 8 9 8 7 6 5 now run this macro and see what happens in column D the macro is Code: Sub test() Dim r As Range, c As Range, mx As Double, dif() As Double Dim j As Integer, k As Integer, m As Integer Worksheets("sheet1").Activate Set r = Range(Range("C2"), Range("C2").End(xlDown)) mx = WorksheetFunction.Max(r) MsgBox mx j = WorksheetFunction.Count(r) MsgBox j ReDim dif(1 To j) For k = 1 To j dif(k) = r.Cells(k, 1) - mx r.Cells(k, 2) = dif(k) If dif(k) < -2 Then r.Cells(k, 2) = "NC" Next k '==================== m = WorksheetFunction.Match(mx, r, 0) + 1 MsgBox m r.Cells(1, 3) = m End Sub the macro fills up the column d with the difference between column c value and the max and also in E2 the row of the maximum the result (colum C D and E will be col C colD col E h3 h4 h5 8 -1 3 9 0 8 -1 7 -2 6 NC 5 NC now you want to loop this action from c3 down, c4 down. etc. iI presume the difference between c value and the NEW max will be filled in the same column D deleting D2. . in that case row of the maximum will be in E3. Is this logic correct On Monday go through this and give your feedback.