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

--
Message posted from http://www.ExcelForum.com

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

Glen
-----Original Message-----
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 columns
of data to find the person with the lowest age but if
there 2
incidences (eg. John & Jeff), it returns the value of
one with the
lowest height.

I am just starting to build the code to perform a
minimum search which
works okay for its simplicity but then I found out that
the function
does not automatically updates itself whenever I change
the table
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 have
it copied here. Pls let me know what I need to do to
ensure that the
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 = LowestValue


---
Message posted from http://www.ExcelForum.com/

.

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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

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

NickHK

"richardbok " wrote in message
...
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 columns
of data to find the person with the lowest age but if there 2
incidences (eg. John & Jeff), it returns the value of one with the
lowest height.

I am just starting to build the code to perform a minimum search which
works okay for its simplicity but then I found out that the function
does not automatically updates itself whenever I change the table
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 have
it copied here. Pls let me know what I need to do to ensure that the
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 = LowestValue


---
Message posted from http://www.ExcelForum.com/