Finding lowest number depending on value from corresponding cell
 

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