VBA Range.Find function on rounded negative number

Question

I'm having an issue with a VBA macro I wrote, with the part that is intended to find a lowest value in range. The line looks like this:

Min = Application.WorksheetFunction.Min(a0eqB) Set MinCell = a0eqB.Find(Min, LookIn:=xlValues)

And it returns Object variable or With block variable not set error. Now, I know why this happens - sometimes Find finds Nothing, and I learned how to handle this type of situations. Although, it also finds Nothing when the value that is dispayed in a cell differs from the value in Min variable.

For instance, when Min = -11.2641373534338, the value in a cell is -11.264137, and then error occurs. But if I change the number of decimal places displayed for that cell via Excel UI buttons until it is -11.2641373534338, everything works fine. Value in a cell is actually a formula calculation result if that helps.

I see two ways of dealing with this issue:

1. Finding out how many decimal places are being displayed and then rounding actual value so they match. But this way matching actually might go wrong, since numbers like 11.321 and 11.322 if rounded to 2 decimal places will be the same. Also, it's not possible due to the fact that I need to find the cell's adress in order to do that, and that's done in line 2 of the code above that's causing an issue.
2. Somehow telling Find function to use actual and not displayed numbers, but I've got no idea on how to do that. I googled for several days but still no success.

I will really appreciate you help.

Answer 1

There are at least two possible problems:

• don't use Min as a variable name
• Dim the variable you do use

For example:

Sub GetLowest()
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    Dim rng As Range, Lowest As Double, WhereIs As Range
    Set rng = Range("A1:F9")

    Lowest = wf.Min(rng)
    Set WhereIs = rng.Find(What:=Lowest, After:=rng(1))
    MsgBox WhereIs.Address
End Sub

when run on:

has no trouble finding B2 regardless of formatting.

Answer 2

Having looked at the issues with negative numbers and using it with the find function; a possible solution would be to format your search range and number to find with the same number format. (Based on code supplied by @Gary's Student)

Code is below for this.

If you don't want to alter the number formats in your spreadsheet, then a further hack would be to make a copy of the worksheet to format and find the address then delete the copy and use the address in the original sheet, but that would involve a bit more coding...

Sub GetLowest()

    Dim sFormat As String: sFormat = "0.000000000"
    Dim wf As WorksheetFunction: Set wf = Application.WorksheetFunction
    Dim rng As Range: Set rng = Range("MinRange")
    rng.NumberFormat = sFormat
    Dim Lowest As Double: Lowest = Format(wf.Min(rng), sFormat)
    Dim WhereIs As Range: Set WhereIs = rng.Find(What:=Lowest)

    MsgBox WhereIs.Address

End Sub