Search code examples
excelexcel-2007vba

Why am I getting Error 2042 in VBA Match?

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 

3

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.

Solution

  • 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)