How to get function to update itself with changes
 

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

How to get function to update itself with changes
 

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/

.

How to get function to update itself with changes
 

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.

How to get function to update itself with changes
 

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/