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

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

SUNIL PATEL wrote:

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

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

-----Original Message-----
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




.