How to get function to update itself with changes

[richardbok]

A simple worksheet data is:

John Maggie Jeff
Age-Height Age Height Age Height
20 1.82 21 1.6 20 1.75

I am trying to create a search_lowest function to scan through column
of data to find the person with the lowest age but if there
incidences (eg. John & Jeff), it returns the value of one with th
lowest height.

I am just starting to build the code to perform a minimum search whic
works okay for its simplicity but then I found out that the functio
does not automatically updates itself whenever I change the tabl
information (eg. changing John's age to 19) does return a 19.

I believe it has to do with the way I address the function and I hav
it copied here. Pls let me know what I need to do to ensure that th
value is updated and "live" whenever changes happen.

Thanks.
richardbok

LowestValue = 999
FoundCount = 0

For lngCol = rngStart.Column To lngLastCol Step 1
If LowestValue > Cells(rngStart.Row, lngCol).Value Then
LowestValue = Cells(rngStart.Row, lngCol).Value
End If
Next lngCol

search_lowest = LowestValu

 

[Guest]

Do you have "Calculate" on "Automatic"?
check in Tools - Options - Calculation

Glen

 

[Beto]

Hi, have you tried adding "Application.Volatile = True" at the beginning
of your code function.

Example:

Sub LowestValue(...) as ....
Dim ...

Aplication.Volatile = True

[Code of the function]

End Sub

It may work.
Regards,

 

[Nick Cranham]

richardbok,
Why not code to just sort the data how you want, then look at the top (or
bottom) row ?

NickHK