无法使用 vba vlookup 查找日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20961614/
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
Unable to lookup a date using vba vlookup
提问by schenker
I have an excel worksheet with column A containing dates and column B containing the respective days. Eg. A2=01/01/14 and B2=Wed and so on.
我有一个 Excel 工作表，其中 A 列包含日期，B 列包含相应的日期。例如。A2=01/01/14 和 B2=Wed 等等。
Im trying to retrieve the days in B2 with a sample vba code, but I keep getting an error that "Run-time error '1004' unable to get the Vlookup property of the worksheetFunction class"
我尝试使用示例 vba 代码检索 B2 中的天数，但我不断收到错误消息“运行时错误‘1004’无法获取 worksheetFunction 类的 Vlookup 属性”
Pls see my code and advise what is wrong. Thanks.
请查看我的代码并告知有什么问题。谢谢。
Sub Button1_Click()
Dim lookup_value As String
Dim lookup_table As Range
Let lookup_value = "1/1/2014"
Set lookup_table = Range("A:B")
the_day = WorksheetFunction.VLookup(CDate(lookup_value), lookup_table, 2, False)
Range("D7") = the_day
End Sub
Pls note that I want to pass lookup_value manually rather than looking up cell A2.
请注意，我想手动传递lookup_value，而不是查找单元格A2。
回答by Ron Rosenfeld
VLookup is sensitive to data types. And your VBA routine is looking for a DATE data type, but such does not exist on an Excel worksheet. So you need to convert your string lookup_value to a data type compatible with what is on the worksheet. This could be a Long (for whole dates) or Double (if you are including times.
VLookup 对数据类型敏感。并且您的 VBA 例程正在寻找 DATE 数据类型，但 Excel 工作表中不存在这种类型。因此，您需要将字符串 lookup_value 转换为与工作表上的内容兼容的数据类型。这可能是 Long （对于整个日期）或 Double （如果您包括时间。
Try:
尝试：
the_day = WorksheetFunction.VLookup(CLng(CDate(lookup_value)), lookup_table, 2, False)
