Why am I getting Error 2042 in VBA Match?

Question

I have Column A:

+--+--------+
|  |  A     |
+--+--------+
| 1|123456  |
|--+--------+
| 2|Order_No|
|--+--------+
| 3|    7   |
+--+--------+

Now if I enter:

=Match(7,A1:A5,0)

into a cell on the sheet I get

As a result. (This is desired)

But when I enter this line:

Dim CurrentShipment As Integer
CurrentShipment = 7
CurrentRow = Application.Match(CurrentShipment, Range("A1:A5"), 0)

CurrentRow gets a value of "Error 2042"

My first instinct was to make sure that the value 7 was in fact in the range, and it was.

My next was maybe the Match function required a string so I tried

Dim CurrentShipment As Integer
CurrentShipment = 7
CurrentRow = Application.Match(Cstr(CurrentShipment), Range("A1:A5"), 0)

to no avail.

Your code works for me as is. The error message you are getting is the equivalent of `#N/A`. Perhaps it is focusing on the wrong sheet sometimes? You might try actively selecting the right sheet before running the code. — techturtle, Mar 20 '13 at 15:08

score 17 · Accepted Answer · edited Sep 05 '18 at 16:25

17

See the list of VBA Cell Error Values:

Constant    Error number  Cell error value
xlErrDiv0   2007          #DIV/0!
xlErrNA     2042          #N/A
xlErrName   2029          #NAME?
xlErrNull   2000          #NULL!
xlErrNum    2036          #NUM!
xlErrRef    2023          #REF!
xlErrValue  2015          #VALUE!

Try converting the value of CurrentShipment from an Integer to a Long instead of to a String:

CurrentRow = Application.Match(CLng(CurrentShipment), Range("A1:A5"), 0)

edited Sep 05 '18 at 16:25

TylerH

20,799
66
75
101

answered Mar 20 '13 at 14:50

Vincent MAURY

253
2
5

2

And it will work for whole numbers, but not for decimal and not for string, so it feels a bit dodgy... – K_B Mar 20 '13 at 15:07
1

This also works for my data where I am matching dates. I really don't know why this is the case since the CLng is converting a value to a long integer. A further explanation is appreciated. – Samuel Song Jan 18 '17 at 21:11

score 17 · Answer 2 · answered Sep 19 '13 at 13:48

As a side note to this and for anyone who gets this error in future, with any function returning a possible error, the variant type works quite well:

Dim vreturn as variant 

vreturn = Application.Match(CurrentShipment, Range("A1:A5"), 0) ' this could be any function like a vlookup for example as well

If IsError(vreturn) Then
    ' handle error
Else
    CurrentRow = cint(vreturn)
End If

score 1 · Answer 3 · answered Mar 20 '13 at 15:18

If you look for match function in object browser it returns double so i have declared the variable CurrentRow as double and while it accepts 3 variant parameter. Try below code if it works for you.

enter image description here

  Sub sample()

    Dim CurrentShipment As Variant
    CurrentShipment = 7

    Dim CurrentRow As Double
    CurrentRow = Application.Match(CurrentShipment, Range("A1:A5"), 0)
    End Sub

score 1 · Answer 4 · answered Mar 01 '22 at 08:48

1

I had exactly the same error message when tried to use the Match function in a workbook with several worksheets. The use of the variant type for the variable that receives the result is good to avoid the running errors, if the subject that we try to match does not exists. But my mistake was in the location of the range used. It is vital to point for the worksheet that have the range to be searched. In the first, time I used Range(...) without the worksheet reference, and the function only worked correctly if I had that worksheet activated.

answered Mar 01 '22 at 08:48

Rui S.

11
2

This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/31184867) – Ike Mar 05 '22 at 17:00
Actually, it raises a very pertinent point: the range needs to belong to the sheet being searched. If a different worksheet is activated, using `Range(...)` alone will fail. – Alex Peters Oct 09 '22 at 02:24

Skip Intro · Answer 5 · 2013-03-20T15:33:24.107

Interestingly, I typed your data into a blank Excel sheet and then ran your original snippet of code. It returned 3, as expected, without having to cast CurrentShipment as String or Long.

Not DIM'ing CurrentRow makes it a Variant by default, but even setting both of them as Integer or CurrentRow as Byte does not throw an error, so using Double as the return type is redundant.

Sub Match()

Dim CurrentShipment As Integer
Dim CurrentRow As Byte '<--- NOTE

CurrentShipment = 7
CurrentRow = Application.Match(CurrentShipment, Range("A1:A5"), 0)

MsgBox CurrentRow

End Sub

score 0 · Answer 6 · answered Mar 20 '13 at 15:09

For me it worked fine without type casting anything. I used both:

Application.WorksheetFunction.Match(CurrentShipment, Range("A1:A5"), 0)

and

Application.Match(CurrentShipment, Range("A1:A5"), 0)

Dimensioned CurrentShipment as Integer, Double, Long or Variant, all worked fine...

Why am I getting Error 2042 in VBA Match?

6 Answers6

Linked