Highest & Lowest

[hsandeep]

Z1=1 or 2 as soon as system time equals 'start time' & 'end time' respectively else Z1=0 (default)
During this "Time Period" when Z1 first becomes equal to 1 & finally when it becomes equal to 2;
C2:C200 & D2 D200 keeps on updating its values continuously.
Output required: in E2:E200 & F2:F200 which should be the highest value of respective cell address of C column & the lowest value of respective cell address of D column AS LONG AS Z1=1.
Ex:
For Z1=1
C2=200.22 D2=507.43 E2=200.22 F2=507.43
C2=201.34 D2=501.43 E2=201.34 F2=501.43
C2=204.75 D2=500.43 E2=204.75 F2=500.43
C2=204.75 D2=508.55 E2=204.75 F2=500.43
C2=203.89 D2=500.43 E2=204.75 F2=500.43
...
Answer is E2 & F2.
Similarly for other C3:C200 & D3:D200 answer would be in E3:E200 & F3:F200.
How to accomplish?

 

[mikerickson]

Perhaps this UDF with the formula =ContinualMax(E2:F200, Z1=1)

Function ContinualMax(MaxRange As Variant, Optional UpdateValue As Boolean = True, Optional ResetValue As Boolean = False)
    Dim currentVal As Double, newVal As Variant

    currentVal = Val(CStr(Application.Caller.Text))
    
    newVal = Application.Max(MaxRange)
    UpdateValue = IsNumeric(newVal) And UpdateValue
    
    If UpdateValue Then
        newVal = Application.Max(MaxRange, currentVal)
    Else
        newVal = currentVal
    End If
    
    If ResetValue Then
        ContinualMax = 0
    Else
        ContinualMax = newVal
    End If
End Function

 

[hsandeep]

I pasted the code in the ws & formula =ContinualMax(E2:F200, Z1=1) in E2. But it gives #NAME?

 

[mikerickson]

That code should go in a normal module, not a worksheet's code module.

 

[hsandeep]

Code pasted in standard module. Still it gives #NAME? Formula in E2=ContinualMax($E$2:$F$200,$Z$1=1).

 

[hsandeep]

mikerickson,

waiting bro...it is unsolved as yet. Thread#24

 

[mikerickson]

I can't figure out what the problem is.
I put the code in a normal module, not the sheet's module, not a class module.
I put the formula in a cell and it worked as expected.
Perhaps if you specified it as a Public Function, rather than relying on the default.

 

[hsandeep]

Just a little bit more....

Help me to paste the code properly by 'detailing' how to put the code in a normal module & not a class module.

 

[hsandeep]

It gives a message box:
Ambiguous Name Detected: ContinualMax

 

[mikerickson]

You have that code in two places, search the VBEditor on ContinualMax to find where they are. Delete one of them.

 

[hsandeep]

I pasted the code in a NEW EXCEL WORKBOOK in a ws (in the normal mode) & formula =ContinualMax(E2:F200, Z1=1) in E2. But E2=#NAME?...still