Finding a floating point value

[INTP56]

I have a data file where one column has a section name and later I have a
column that is a rolling average of another column.

I have a routine that finds the range of that column based on section name
and assigns it to a variable, rngTarget. I've validated this part of the code
is selecting the correct range

Now, in a loop for each section, I do the following:

dblAveMinValue = Application.WorksheetFunction.Min(rngTarget)
Set rngTargetMinCell = rngTarget.Find( _
What:=dblAveMinValue, LookAt:=xlWhole, LookIn:= xlValues)
If Not rngTargetMinCell is Nothing Then
With rngTargetMinCell.Borders
.LineStyle = xlContinuous
.Weight = xlThick
End With
Else
MsgBox "Could Not find cell with value " & cstr(dblAveMinValue)
End If

There is always the right value in dblAveMinValue, but .Find can't find it.
I tried using varAveMinValue (Declared as Variant) but it makes no
difference. I realize there are issues with searching for Floating point
numbers, but I was hoping that searching for the value I just found would
work.

I'm wondering if how Excel stores the data in the worksheet is different
than how the value is represented as a double. Of course, I can pull the
range into a variant, iterate throught the range myself looking for the min
value, and save the index to translate it back to the sheet.

Is this the best way to accomplish this task?

Bob

 

[Jim Thomlinson]

Instead of doing the whole find thing you could do something like this...

Sub test()
Dim Target As Range
Dim lngRow As Long

Set Target = Range("A1:A7")
lngRow = Application.Evaluate("=MATCH(MIN(" & _
Target.Address & "), " & Target.Address & ",FALSE)")
End Sub

it uses the match function to return the row where the min value was found...

 

[INTP56]

Thank you Jim,

Here is the version I will be using:

With Application.WorksheetFunction
lngMinIndex = .Match(.Min(rngTarget),rngTarget, False)
End with
Set RangeTargetMinCell = rngTarget.Cells(lngMinIndex,1)

If Not rngTargetMinCell is Nothing Then
With rngTargetMinCell.Borders
.LineStyle = xlContinuous
.Weight = xlThick
End With
End If

Bob

 

[INTP56]

Actually, it looks like I have to check first to make sure rngTarget has more
than one row, or Match fails.

Bob