0

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.

Improve this question
2
  • Maybe it is an issue with decimals that you don't see? What happens if you set lookat:=xlpart in your find parameters? Also, bestPnL is what you're looking for, and it is a double. Make sure the values you're looking at are also numeric, I've had problems with that before as well. For example if you write What:=Cstr(bestPnL) see if it works? Commented Jul 8, 2016 at 18:18
  • tried casting to string, that didnt work Commented Jul 8, 2016 at 19:09

2 Answers 2

Reset to default
6

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Without changing anything else, I tried using xlFormulas that did not work, as you ended with "will work even with the thousand separator." I also added Searchorder:=xlByColumns since its a horizontal one row range its searching
Did you try removing the thousands separator?
...Since I don't want to remove the separator from the sheet, I'm going to add it to the search variable and see if that works. Yah, that wont work. Ill try reformatting the range while searching and then putting it back at the end.
Sorry I was testing with hard-coded numbers in a range: if I switch those to formulas (with the same values) then Find() fails.
I tried setting tempRange.NumberFormat = "0.00" before the find and changing back to LookIn:=xlValues and that worked. Edit your post and Ill accept
|
1

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.

Improve this answer

4 Comments

Question is not about looking for 0 though? Or are you just using that as an example?
Indeed, I was lost in my ideas, but that should work with any integer value. For decimals, we all know Excel can be...tricky
It does work, even for decimals and currency formatting, so provided the OP only wanted to search a single row/column it would probably be a better approach than Find
The advantage of Find is that it allows a backward search.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.