0

I have a short row vector (0.0, 1.0, 0.1, 0.9, 0.8, 0.2, 0.3, 0.7, 0.6, 0.4, 0.5) in Excel. I try to pick the index by rank using MATCH on a LARGE argument. The printout indicates it does not work. Could you please comment / help?

Sub rank()
Dim rng As Range
Dim sample As Variant
Dim reference As Single
Dim j As Long

Set rng = Sheets("Sheet1").Range("B3").CurrentRegion
sample = rng.Value
' Debug.Print LBound(sample, 2); UBound(sample, 2)

For j = LBound(sample, 2) To UBound(sample, 2)
    Debug.Print j, Application.Large(Application.Index(sample, 1, 0), j), _
    Application.Match(Application.Large(Application.Index(sample, 1, 0), j), sample)
Next j

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
RonGd
  • 1
  • *How* does the printout indicate it does not work? Do you get any return value from your `Debug.Print` statement? Is it not the value you expect? Does it throw an error? Please update your question to clarify these points and perhaps include some sample data and the output you get vs. your expected output. – Samuel Everson May 25 '20 at 22:25
  • I threw some dummy data into a new worksheet and copied your code into it's module. I saw the `Match` function returns `Error 2042`. Does this answer your question? [Why am I getting Error 2042 in VBA Match?](https://stackoverflow.com/questions/15526784/why-am-i-getting-error-2042-in-vba-match) – Samuel Everson May 25 '20 at 22:34
  • Why do you assign the CurrentRegion to the variable `sample` (a range of a size you can't possibly know unless you just created the sheet in the same procedure, which you didn't) and then use this variable to search in whereas all you really mean is a section of column B? Your code needs a check on what the MATCH function returns before that value can be used. However, the beginning of your trouble is in the missing logic in declaring the `sample` range. – Variatus May 26 '20 at 00:41
  • The 3rd parameter of `MATCH` is missing, so it defaults to `1`. Use `0` to perform exact search. – BrakNicku May 26 '20 at 04:09
  • The array is in the sheet. Otherwise it does return error 2042 and another one, 2036 or something close to it. The printout is made of three columns, the last is the match result, where none of the results should be repeated: 11,11,11,11,9,7,7,7,7,6,3,1. – RonGd May 26 '20 at 12:02
  • BrakNicku - You figured it out! Thanks so much! Works like a charm! – RonGd May 26 '20 at 12:17

0 Answers0