PROBLEM FINDING MINIMUM

[SUNIL PATEL]

Minval = Application.WorksheetFunction.Min(Range(Cells(1, 6), Cells(1, 8)))
CHEAPCOL% = Rows(1).Find(Minval, Cells(1, 5), LookIn:=xlFormulas).Column

column 6 7 8
where row 1 reads 12.35 12.30 12.53

code returns 12.3 as the minimum value (correct)
But CHEAPCOL% is returned as column 6 (wrong)
How can this be resolved?

Thanks

Sunil

 

[Dave Peterson]

Your code worked ok for me in light testing, but I'd limit my range and just
check in values:

Option Explicit
Sub testme()

Dim minVal As Double
Dim CheapCell As Range
Dim CheapCol As Long

With ActiveSheet
With .Range(.Cells(1, 6), .Cells(1, 8))
minVal = Application.Min(.Cells)
Set CheapCell = .Find(what:=minVal, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole)
If CheapCell Is Nothing Then
CheapCol = 0
Else
CheapCol = CheapCell.Column
End If
End With
MsgBox CheapCol
End With
End Sub

 

[Anya]

If you change your code to
CHEAPCOL% = Rows(1).Find(Minval, Cells(1, 6),
LookIn:=xlFormulas).Column
it will return column 7