VBA，MATCH 函数没有返回正确的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6519706/
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
VBA, MATCH function does not returns the correct value
提问by Andrew Strathclyde
I have a problem with calling the Match function from a VBA code: it returns the wrong values. I have a speadsheet like this (it's an example):
我在从 VBA 代码调用 Match 函数时遇到问题：它返回错误的值。我有一个这样的电子表格（这是一个例子）：
a b c d
1 testRange testValue
2 01/01/2011 00:00 03/01/2011 00:00 testValue = A4
3 02/01/2011 00:00
4 03/01/2011 00:00 result from match function
5 04/01/2011 00:00 3 testResult = MATCH(C2,A2:A20,1)
6 05/01/2011 00:00
7 06/01/2011 00:00 testResult
8 07/01/2011 00:00 9
9 08/01/2011 00:00
10 09/01/2011 00:00
11 10/01/2011 00:00
12 11/01/2011 00:00
13 12/01/2011 00:00
14 13/01/2011 00:00
15 14/01/2011 00:00
16 15/01/2011 00:00
17 16/01/2011 00:00
18 17/01/2011 00:00
19 18/01/2011 00:00
20 19/01/2011 00:00
the range that I'm searching is in A1:A20, and I'm looking for the value wrote in C2, that as you can see is just a value of the range (this to be sure that the value is in the range). I expect the Match function to return the number 3, like it does when called from the spreadsheet (cell C5), but the function called in VBA returns the value 9 (cell C10). I used this code:
我正在搜索的范围在 A1:A20 中，我正在寻找在 C2 中写入的值，正如您所看到的只是范围的值（这是为了确保该值在范围内） . 我希望 Match 函数返回数字 3，就像从电子表格（单元格 C5）调用时一样，但在 VBA 中调用的函数返回值 9（单元格 C10）。我使用了这个代码：
Sub testMatch()
testRange = Range("A2:A20")
testValue = Range("C2")
testResult = Application.WorksheetFunction.Match(testValue, testRange, 1)
Range("C8") = testResult
End Sub
I have already tried to cast testValue as Date and as Double (solutions suggested in other forums), but with Date I have the same result and with Double I get the Error 1004 "Unable to get the Match property of the WorksheetFunctions class"
我已经尝试将 testValue 转换为 Date 和 Double（其他论坛中建议的解决方案），但是使用 Date 我得到相同的结果，使用 Double 我得到错误 1004“无法获取 WorksheetFunctions 类的 Match 属性”
Any suggestion?
有什么建议吗？
Thanks
谢谢
回答by JMax
As suggested in other forums, you should type your variables (Date...) and use Option Explicit.
正如其他论坛中所建议的，您应该输入变量（日期...）并使用Option Explicit.
In this case, the error comes from the use of dates inside the matchfunction, try this :
在这种情况下，错误来自match函数内部使用日期，试试这个：
Application.WorksheetFunction.Match(CLng(testValue), testRange, 0)
(edit > source: thanks to this thread)
（编辑> 来源：感谢这个线程）
I've tested on your example and it works for me.
我已经对你的例子进行了测试，它对我有用。
Regards,
问候，
Max
最大限度
