0

I have some simple code to find a max of a range and then return the row where the max value is located. For some reason, there are instances where the Match returns an Error 2042, but most of the time it runs successfully. How is it that it can't find a match in a range when the value I'm searching for is the max value of that same range?

The values in the range are percentages with many significant digits (eg 0.992149823976789%).

Sub test()

dim rng as range

Set rng = Range("A1:A100")

rng_max = application.max(rng)

max_row = application.match(cdbl(rng_max), rng.value, 0)

End Sub
Korean_Of_the_Mountain
  • 1,428
  • 3
  • 16
  • 40
  • 1
    How is `rng_max` declared, why do you apply `cdbl(rng_max)`, and does it happen when you call both `max` and `match` on the same thing (either on `rng` or on `rng.value`, but not both)? – GSerg Apr 13 '17 at 19:05
  • I have not declared rng_max as anything, anywhere. I use CDbl after I saw a different post here about having issues with Match. I just tried using rng.value instead of rng to see if it would help, and it did work for a few values, but clearly not all. It might not do anything, but I also didn't think it would necessarily hurt. – Korean_Of_the_Mountain Apr 13 '17 at 19:21
  • 1
    Try declaring `rng_max` as something (like `Double`), do not use `CDbl` and call `max` and `match`on the same thing. [Floating point math is not broken](http://stackoverflow.com/q/588004/11683), but it will be for you if you first coerce values back and forth and then expect them to match exactly. – GSerg Apr 13 '17 at 19:36
  • Ah ok cool. That seems to work now. – Korean_Of_the_Mountain Apr 13 '17 at 20:08

1 Answers1

-1

It worked fine no mater wheter I use cdbl() or not:

enter image description here

Moreno
  • 608
  • 1
  • 9
  • 24
  • ["it did work for a few values, but clearly not all"](http://stackoverflow.com/questions/43399844/why-is-application-match-is-inconsistent#comment73860897_43399844) – GSerg Apr 13 '17 at 20:33
  • Clearly not all since I'm unable to test it for "all values" there are infinite Real numbers right? @GSerg – Moreno Apr 14 '17 at 03:03
  • Correct. And it is exactly why your statement that it "worked fine no mater wheter I use cdbl() or not" makes no sense. It would only make sense if you tested it on all possible numbers. Since that is not possible, your answer does not prove anything. Please refer to http://stackoverflow.com/q/588004/11683 for the root of the problem. – GSerg Apr 14 '17 at 06:45