Simple question to find min value

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

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" <Tre_cool.1u6jft_1124738110.6554@excelforum-nospam.com> wrote in message
news:Tre_cool.1u6jft_1124738110.6554@excelforum-nospam.com...
>
> 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
news:ONpzG%230pFHA.3804@TK2MSFTNGP10.phx.gbl...
> 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" <Tre_cool.1u6jft_1124738110.6554@excelforum-nospam.com> wrote in message
> news:Tre_cool.1u6jft_1124738110.6554@excelforum-nospam.com...
>>
>> 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" <Tre_cool.1u6jft_1124738110.6554@excelforum-nospam.com> wrote in
message news:Tre_cool.1u6jft_1124738110.6554@excelforum-nospam.com...
>
> 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!