CODEWITHSUNDEEP
vbaexcel
dinotom
dinotom

Reputation: 5162

VBA Range.Find method not finding a value that IS in the range

I have the following method which finds the largest and smallest values in a range. I am then using those values to locate the actual cell they are in as I need to grab the value from the header cell of that column. The Range.Find is always returning Nothing even though the range being searched HAS A CELL WITH THAT VALUE.

Sub GetTopAndBottomFiveCommodities()
    Dim tempRange As Range, x As Integer, bestPnL As Double, worstPnL As Double
    Dim strTopRangeName As String, strBottomRangeName As String
    Dim cCell As Range, commodityName As String

    Set tempRange = dataSourceSheet.Range("A:A").Find(What:="Year Totals")
    Set tempRange = Range(tempRange.Offset(0, 1), tempRange.End(xlToRight).Offset(0, -1))
    For x = 1 To 5
        strTopRangeName = "TopCommodity" & CStr(x)
        strBottomRangeName = "BottomCommodity" & CStr(x)
        bestPnL = WorksheetFunction.Large(tempRange, x)
        worstPnL = WorksheetFunction.Small(tempRange, x)
        Debug.Print tempRange.Address
        ' get the top commodity name and PnL       
        **Set cCell = tempRange.Find(What:=bestPnL, LookIn:=xlValues)**
        commodityName = dataSourceSheet.Cells(5, cCell.Column).Value
        Range(strTopRangeName).Value = commodityName
        Range(strTopRangeName).Offset(0, 1).Value = bestPnL
    Next x
End Sub

The code line

Set cCell = tempRange.Find(What:=bestPnL, LookIn:=xlValues)

is always returning nothing but I have verified that there are cells with that value. One example, the cell value is 66,152.61 (displayed in cell as 66,153) and the bestPnL variable is 66,152.61 , so I tried rounding bestPnL to 66,153, but still didn't find it. The debug statement is showing tempRange has the right range, so its not searching in the wrong place.

The only thing I can think of is the cell with the value, gets its value from a very long formula, using over a dozen named ranges, can this be fouling the find method?

Just so we all know I'm not crazy, here is a snapshot of part of the range I'm searching where I'm testing.

enter image description here

EDIT Based on Tim Williams suggestion, I changed the number format of the range being searched prior to the Find call.

 tempRange.NumberFormat = "0.00"

and then the Find call works as it should. I then just put the number format back the way I want it at the end of the routine.

tempRange.NumberFormat = "$#,##0;[Red]$#,##0"

Works as expected now.

Upvotes: 0

Views: 9208

Answers (2)

iDevlop
iDevlop

Reputation: 25252

This is an old question, but I found an alternative that can be effective and simple in some situations:

dim idx as long, rng as range
set rng = someRange
idx = application.WorksheetFunction.Match(1234,rng,0)

This will return the relative position of the FIRST 1234 valued cell in the provided range, independently of the formatting. The last 0 means you use an exact match.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166126

Try removing the thousand separator from the number format on the cells. When I did that in a test range it worked fine, but with the separator it failed to find the value. 

Set f = rng.Find(what:=bestPnL, LookIn:=xlFormulas)

will work even with the thousand separator (EDIT: only works with hard-coded values; fails with formulas).

EDIT2: this worked for me with a thousands separator and using formulas for the values (EDIT3!: does not work with currency formatting).

Sub Tester()

    Dim f As Range, v, rng As Range

    Set rng = Range("C3:C21")

    v = Application.Large(rng, 3)

    v = Format(v, rng.Cells(1).NumberFormat)

    Set f = rng.Find(what:=v, LookIn:=xlValues)

    Debug.Print f.Address   ' >> C19

End Sub

Upvotes: 6

Related Questions