为什么我在 VBA Match 中收到错误 2042？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15526784/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why am I getting Error 2042 in VBA Match?
提问by user2140261
I have Column A:
我有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"
CurrentRow 获取值“错误 2042”
My first instinct was to make sure that the value 7 was in fact in the range, and it was.
我的第一直觉是确保值 7 确实在范围内，而且确实如此。
My next was maybe the Match function required a string so I tried
我的下一个可能是 Match 函数需要一个字符串，所以我试过了
Dim CurrentShipment As Integer
CurrentShipment = 7
CurrentRow = Application.Match(Cstr(CurrentShipment), Range("A1:A5"), 0)
to no avail.
无济于事。
采纳答案by Vincent MAURY
See the list of VBA Cell Error Values:
请参阅 VBA单元格错误值列表：
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!
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 CurrentShipmentfrom an Integerto a Longinstead of to a String:
尝试将值CurrentShipment从 an转换Integer为 aLong而不是 a String：
CurrentRow = Application.Match(CLng(CurrentShipment), Range("A1:A5"), 0)
回答by KDT
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
回答by Vincent MAURY
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.
如果您在对象浏览器中查找匹配函数，它将返回 double，因此我已将变量 CurrentRow 声明为 double 并且它接受 3 个变体参数。如果它适合您，请尝试下面的代码。
Sub sample()
Dim CurrentShipment As Variant
CurrentShipment = 7
Dim CurrentRow As Double
CurrentRow = Application.Match(CurrentShipment, Range("A1:A5"), 0)
End Sub
回答by Skip Intro
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.
有趣的是，我将您的数据输入到空白 Excel 工作表中，然后运行您的原始代码片段。正如预期的那样，它返回 3，而不必将 CurrentShipment 转换为 String 或 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.
Not DIM'ing CurrentRow 默认使其成为 Variant，但即使将它们都设置为 Integer 或 CurrentRow 作为 Byte 也不会引发错误，因此使用 Double 作为返回类型是多余的。
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
回答by K_B
For me it worked fine without type casting anything. I used both:
对我来说，它在没有类型转换任何东西的情况下工作得很好。我两个都用过：
Application.WorksheetFunction.Match(CurrentShipment, Range("A1:A5"), 0)
Application.WorksheetFunction.Match(CurrentShipment, Range("A1:A5"), 0)
and
和
Application.Match(CurrentShipment, Range("A1:A5"), 0)
Application.Match(CurrentShipment, Range("A1:A5"), 0)
Dimensioned CurrentShipment as Integer, Double, Long or Variant, all worked fine...
Dimensioned CurrentShipment 为 Integer、Double、Long 或 Variant，一切正常...
