Bob Phillips
12/19/2006 10:53:00 PM
Dim rng As Range
Set rng = Range(Range("F4"), Range("F4").End(xlDown))
MsgBox "The Lowest number is " & ActiveSheet.Evaluate("MIN(IF(" &
rng.Address & _
"=""F""," & rng.Offset(0, 1).Address & "))")
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"Cathain" <Cathain@discussions.microsoft.com> wrote in message
news:8AA68EE5-A0EF-44E9-A37E-3A210AF7862D@microsoft.com...
>I have two columns side by side. One has a list of numbers and the other
>has
> either "M" or "F". So what I'm trying to do is find out the lowest value
> for
> all the F's. Also the code has to take into account if extra rows are
> added.
> Now I can find the lowest value in the column with numbers fine its just
> everything I tried to take into account the column with the F's and M's
> isnt
> working. My code I have so far is what I have for finding the lowest
> value:
>
> Private Sub cmdLowest_Click()
>
> Dim rng As Range, i As Integer, Lowest As Variant
>
>
> Range("F4").Select
> Range(Selection, Selection.End(xlDown)).Select
> Set rng = Selection
>
> Lowest = rng.Cells(1).Value
> For i = 2 To rng.Count
>
> If rng.Cells(i).Value < Lowest Then
> Lowest = rng.Cells(i).Value
> End If
>
> Next i
>
>
> MsgBox "The Lowest number is " & Lowest
>
> End Sub