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

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.

Answer 1

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

Answer 2

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)