Finding cell address for minimum number

[Tre_cool]

I'm trying to find the cell address for the lowest number in a column.
This does not seem to be working when the number is 0. Can someone
please help me figure out what's wrong with this code. It works when
there aren't any 0's in the column.

Thanks in advance
Trevor

Function MinAddress(rng)

Set rng = Columns(22)

' Sets variable equal to minimum value in the input range.
MinNum = Application.Min(rng)


' Loop to check each cell in the input range to see if equals the
' MinNum variable.
For Each cell In rng.Cells
If cell = MinNum Then
' If the cell value equals the MinNum variable it
' returns the address to the function and exits the loop.

MinAddress = cell.Address
Exit For
End If

Next cell

End Function

 

[Bernie Deitrick]

Tre_Cool,

Try it as defined below, used like this from the worksheet

=MinAddress(V:V)

or like this from a macro

Dim myAdd As String
myAdd = MinAddress(Range("V:V"))
MsgBox myAdd

You really don't want to hardcode the column number like you did. Note that
it will return the address of the first instance of the minimum value, if
more than one exist.

HTH,
Bernie
MS Excel MVP


Function MinAddress(rng As Range) As String
MinAddress = rng(Application.Match( _
Application.Min(rng), rng, False)).Address
End Function

 

[Guest]

I tried your function and it worked for positive, negative and zero. The
function is "sluggish". You have range as an argument and also set it inside
the function. As values are changed or added to column V, Excel isn't smart
enought to re-calculate the function because it does not see the input
changing.

I had to keep kicking it with CNTRL-ALT-F9.

You might consider removing the Set in the function and use it as
=MinAddress(V:V)

 

[Tre_cool]

I don't understand where I'd put in MinAddress(Range("V:V"))? Do I kee
MinNum?

Thanks
Trevo

 

[Bernie Deitrick]

See my earlier reply.

HTH,
Bernie
MS Excel MVP

 

[Tre_cool]

How do I call the function?? I tried MinAddress(rng), that didn't work.

Thank

 

[Bernie Deitrick]

As defined below, used like this from the worksheet

=MinAddress(V:V)
=MinAddress(A1:A100)
=MinAddress(rng)
The use immediately above only works if you have a range named "rng" on the
worksheet (Set up through Insert Names etc...)

Or use it like this from a macro

Dim myAdd As String
myAdd = MinAddress(Range("V:V"))
MsgBox myAdd

Or, if you have a defined range variable named rng:

Dim myAdd As String
Dim rng As Range
Set rng = Range("A1:A100")
myAdd = MinAddress(rng)
MsgBox myAdd

HTH,
Bernie
MS Excel MVP


Function MinAddress(rng As Range) As String
MinAddress = rng(Application.Match( _
Application.Min(rng), rng, False)).Address
End Function

 

[Tre_cool]

Bernie, I tried the following one. It worked for every number except 0.
If there was a 0 in the range, it didn't work. Please help. Thanks!

Dim myAdd As String
Dim rng As Range
Set rng = Range("A1:A100")
myAdd = MinAddress(rng)
MsgBox myAdd

 

[Bernie Deitrick]

Tre,

Works for me. Try the test sub below.

HTH,
Bernie
MS Excel MVP

Sub test()
Dim myAdd As String
Dim rng As Range
Set rng = Range("A1:A100")
rng.ClearContents
rng.Value = 1
rng(3).Value = 0
myAdd = MinAddress(rng)
MsgBox "The zero value is in cell " & myAdd

End Sub
Function MinAddress(rng As Range) As String
MinAddress = rng(Application.Match( _
Application.Min(rng), rng, False)).Address
End Function

 

[Tre_cool]

Awesome!! Thanks a lot Bernie...worked perfectly!