View Full Version : Solved: Pick up peak(maximum) value in running data
muzammel
01-15-2010, 09:57 AM
hi all,
I need help on how to pick the peak value in the running data using array formula.
example;
runnning data at B2
the peak(max) value from B2 will be at B3...
can some one assist me...
thanks
Bob Phillips
01-15-2010, 11:16 AM
Can you explain why the peak of B2 is B3?
muzammel
01-15-2010, 09:09 PM
sory xld...
maybe my explanation not so good..
at column B2 the value is extract from other program, so the data will up and down..so that it won't stop....
so...i need pick up the value in the column B2 which is the maximum (peak) and
i need to remain the peak value even the new data is decrease
here an example...
let say the data at column B2 shown
-1
3
6
-2
at column B3 it will select number 6 as the maximum value...
if new high shown at column B2, the new value at column B3 will shown to replace number 6
Bob Phillips
01-16-2010, 07:40 AM
Try thius
Private mcPrevValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value2 > Me.Range("B3").Value2 Then
Me.Range("B3").Value = .Value2
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$2" Then mcPrevValue = Target.Value
End Sub
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
muzammel
01-16-2010, 10:26 AM
thank for the reply...
i will try the code and will feedback on monday since i will start work on monday..
btw..if i need to use more than one column for the same function, is it just add column name on the code...
example is like this;
B2 = B3
C2 = C3
D2 = D3
Please advise...thank you
muzammel
01-18-2010, 06:42 AM
sory xld...
I already copy and paste the program but cannot run...
since my knowledge is zero about VBA, if u don't mind to do compilation for me...
I'm very2 appreciate your help...tahnk you in advance
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.