Why am I getting Error 2042 in VBA Match?

excel, excel-2007, vba

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)

Problem

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.

Original source