automatic updating cell

  • Thread starter Thread starter j feka
  • Start date Start date
J

j feka

I'd like to create a function that will automatically
store a new high value of another cell when it occurs,
but will not change if that first cell's value drops.

I've tried something like

Function NuHi(OldHi, PriceNow)
NuHi = OldHi
If PriceNow > OldHi Then
NuHi = PriceNow
OldHi = PriceNow
End If
End Function

But this won't work. If I remove the
OldHi = PriceNow line, the function will take a value
from another cell which is put in the OldHi parameter,
but if that value goes down, the function gives a lower
value.

Can anyone help?
 
Try this:

Function NewHigh(checkCell As Range)
Static oldhigh
NewHigh = oldhigh
If checkCell.Value > oldhigh Then
oldhigh = checkCell.Value
NewHigh = checkCell.Value
End If
End Function

Regards

Trevor
 
Thanks Tevor,

I don't think this will do the trick because the oldhigh
will be there when I want to use the function in a
different cell.

Simple example of what I want to do.

In cell B1 store a high value, then if A1>B1 replace the
value in B1 with A1.

Now, using the same function I will want to test

A2 against the value in B2
and
A3 against the value in B3

Each row may have totally different high values.

Jack
 
Thanks for the try Trevor, but unless I'm mistaken your approach won't
work if I have more than one oldhigh.

Suppose I have Newvalue in cell A1 and the stored HighValue in cell B1.
If I use the function NewHigh(A1) in B1 the value which is in there will
be tested against oldhigh.

Now suppose I have another Newvalue (different value) in A2 and I want
to test it against a value which is unique to that row which I store in
B2. If I put the function NewHigh(A2) into B2, won't I be testing it
against the value which was established in Row 1?

I intend this function to work in multiple rows, not just one or two.

Am other ideas?
Jack
 
Back
Top