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


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=475552

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


"Tre_cool" wrote in
message ...

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


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile:
http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=475552

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)
--
Gary''s Student


"Tre_cool" wrote:


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


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=475552

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

Thanks
Trevo

--
Tre_coo
-----------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...fo&userid=2641
View this thread: http://www.excelforum.com/showthread.php?threadid=47555

See my earlier reply.

HTH,
Bernie
MS Excel MVP


"Tre_cool" wrote in message
...

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

Thanks
Trevor


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=475552

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

Thank

--
Tre_coo
-----------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...fo&userid=2641
View this thread: http://www.excelforum.com/showthread.php?threadid=47555

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" wrote in
message ...

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

Thanks


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile:
http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=475552

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


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=475552

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" wrote in message
...

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


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=475552

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

--
Tre_coo
-----------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...fo&userid=2641
View this thread: http://www.excelforum.com/showthread.php?threadid=47555