I'm running the macro below to find the minimum value, but the compiler
keeps giving me an error at 'If cell = MinNum Then' . Can someone see
what's wrong with this?

Function MinAddress(rng)

Set rng = Columns(22)
' Sets variable equal to maximum value in the input range.
MinNum = Application.Min(rng)
' Loop to check each cell in the input range to see if equals the
' MaxNum variable.

For Each cell In rng
If cell = MinNum Then
' If the cell value equals the MaxNum variable it
' returns the address to the function and exits the loop.
MinAddress = cell.Address
Exit For
End If
Next cell

End Function

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=397876

Trevor,

There's a lot that's not optimal with your code, but it fails because you are looping through using
columns, which don't have a value property.

Change

For Each cell In rng
(which really means For each column in the columns of range rng )

to

For Each cell In rng.Cells

HTH,
Bernie
MS Excel MVP


"Tre_cool" wrote in message
...

I'm running the macro below to find the minimum value, but the compiler
keeps giving me an error at 'If cell = MinNum Then' . Can someone see
what's wrong with this?

Function MinAddress(rng)

Set rng = Columns(22)
' Sets variable equal to maximum value in the input range.
MinNum = Application.Min(rng)
' Loop to check each cell in the input range to see if equals the
' MaxNum variable.

For Each cell In rng
If cell = MinNum Then
' If the cell value equals the MaxNum variable it
' returns the address to the function and exits the loop.
MinAddress = cell.Address
Exit For
End If
Next cell

End Function

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=397876

It works fine if you remove this line

Set rng = Columns(22)

What is it for?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tre_cool" wrote in
message ...

I'm running the macro below to find the minimum value, but the compiler
keeps giving me an error at 'If cell = MinNum Then' . Can someone see
what's wrong with this?

Function MinAddress(rng)

Set rng = Columns(22)
' Sets variable equal to maximum value in the input range.
MinNum = Application.Min(rng)
' Loop to check each cell in the input range to see if equals the
' MaxNum variable.

For Each cell In rng
If cell = MinNum Then
' If the cell value equals the MaxNum variable it
' returns the address to the function and exits the loop.
MinAddress = cell.Address
Exit For
End If
Next cell

End Function

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=397876

Trevor,

Here's a better way to do this, no looping, variables declared, function has value passed rather
than set:

Sub TryNow()
MsgBox MinAddress(Columns(22))
End Sub

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


--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Trevor,

There's a lot that's not optimal with your code, but it fails because you are looping through
using columns, which don't have a value property.

Change

For Each cell In rng
(which really means For each column in the columns of range rng )

to

For Each cell In rng.Cells

HTH,
Bernie
MS Excel MVP


"Tre_cool" wrote in message
...

I'm running the macro below to find the minimum value, but the compiler
keeps giving me an error at 'If cell = MinNum Then' . Can someone see
what's wrong with this?

Function MinAddress(rng)

Set rng = Columns(22)
' Sets variable equal to maximum value in the input range.
MinNum = Application.Min(rng)
' Loop to check each cell in the input range to see if equals the
' MaxNum variable.

For Each cell In rng
If cell = MinNum Then
' If the cell value equals the MaxNum variable it
' returns the address to the function and exits the loop.
MinAddress = cell.Address
Exit For
End If
Next cell

End Function

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=397876

Thanks Bernie...worked great and much more efficient!


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