Error 2042 on application.match vba decimal numbers

Question

I cannot match the maximum value of a range into the same range. This with the purpose to know in which column i can find the maximum value.

im trying with this code

    Set rango = Range(Cells(45, W), Cells(46, W1))
                rango.Select
                maximo = WorksheetFunction.Max(rango)
               matching = Application.Match(CLng(maximo), Sheets("Dinamicos").Range(Cells(45, W), Cells(46, W1)), 0)

but i get the 2042 error. In this Section im trying to match percentages if i use the code

    Set rango = Range(Cells(45, W), Cells(46, W1))
                rango.Select
                maximo = WorksheetFunction.Max(rango)
               matching = WorksheetFunction.Match.Match(CLng(maximo), Sheets("Dinamicos").Range(Cells(45, W), Cells(46, W1)), 0)

i get the runtime error 1004 Unable to get the Match property of the WorksheetFunction

You say you are having trouble with decimal numbers but you are using `CLng(maximo)` which is a long integer. Which is it? — , Jun 16 '17 at 14:19
yes, i have tried to solve this problem with Clng, but at the begining i left this decimal number without Clng and doesnt work either. i Saw this solution in a similar case so i try it in my code. — Andrea Santos Ruiz, Jun 16 '17 at 14:23
`.Range(.Cells(45, W), .Cells(46, W1)`needs to be a single row or single column. This looks like two rows and more than a single column. — , Jun 16 '17 at 14:28
related: [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) — , Jun 16 '17 at 14:32
Update: `.Range(.Cells(45, W), .Cells(46, W1)` can be multiple rows/column for `MAX` but not for `MATCH`. — , Jun 16 '17 at 14:34

score 0 · Accepted Answer · 2017-06-16T15:09:11.513

0

CLng converts to a Long Integer essentially chopping off any decimal. Use CDbl to convert to a double.

dim maximo as double, matching as variant
with worksheets("sheet1")
    maximo = WorksheetFunction.Max(.Range(.Cells(45, W), Cells(.46, W1)))
end with
with worksheets("Dinamicos")
    matching = Application.Match(CDbl(maximo),  .Range(.Cells(45, W), .Cells(46, W)), 0)
    if not iserror(matching) then
        'found a match in W; do something
        debug.print "found in W"
    else
        matching = Application.Match(CDbl(maximo),  .Range(.Cells(45, W1), .Cells(46, W1)), 0)
        if not iserror(matching) then
            'found a match in W1; do something
            debug.print "found in W1"
        end if
    end if
end with

You also left some dangling cell references in Sheets("Dinamicos").Range(Cells(45, W), Cells(46, W1)) that I have attempted top tighten up.

edited Jun 16 '17 at 15:09

answered Jun 16 '17 at 14:26

`.Range(.Cells(45, W), .Cells(46, W1)`needs to be a single row or single column. This looks like two rows and more than a single column. – Jun 16 '17 at 14:28
Update: `.Range(.Cells(45, W), .Cells(46, W1)` can be multiple rows/column for `MAX` but not for `MATCH`. – Jun 16 '17 at 14:35
Is it possible to match a variable to discontinuous range ? i've been trying to use match function unsuccessfully. Do you have any sugerences to solve this problem ? – Andrea Santos Ruiz Jun 16 '17 at 15:05
Yes, I was thinking that if it failed matching on the `W` column then look in the `W1` column. I'll make an edit. – Jun 16 '17 at 15:06

Error 2042 on application.match vba decimal numbers

1 Answers1