W10上的Excel 2013:不同工作表中的VBA Vlookup产生错误1004

问题描述

我正在开发一个应用程序，该应用程序使用规范代码在另一个电子表格中查找这些代码，并将供应商编号从第二个电子表格返回到第一个电子表格，并在与规范代码相同的列中列出它们.

I'm developing an application which uses specification codes to vlookup those codes in another spreadsheet, and return vendor numbers from the second spreadsheet to the first, listing them in the same column with the specification code.

ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(specsec, [Vendorspec.xlsx!vid], 4)

在上面的代码行中:

• specsec是规范代码，格式为XX XX XX.XX
• vlookup目标文件的名称为"VendorSpec.xlsx".在此工作表中，每个代码在第1列中都是唯一的条目，在第4列中是多个供应商编号中的第一个.将来的代码将在各列中循环，返回当前代码的所有后续供应商ID.

代码行产生错误运行时错误'1004':无法获取工作表函数类的vlookup属性".

The code line produces error "Run-time error '1004': Unable to get the vlookup property of the worksheet function class".

任何人都可以提出修复建议吗?

Can anyone suggest a fix?

谢谢.

推荐答案

您的查找完全失败，并且错误消息完全令人误解.

Your lookup is simply failing, and the error message is utterly misleading.

不是VBA找不到 WorksheetFunction.VLookup 成员，只是您的VLookup引发了错误.

It's not that VBA couldn't find the WorksheetFunction.VLookup member, it's just that your VLookup raised an error.

您需要:

• 使用 On Error GoTo 语句处理该运行时错误

• Handle that runtime error with an On Error GoTo statement

或

• 使用最新版本的 Application.VLookup ，它不会为您提供 IntelliSense ，但它不会在查找失败时抛出运行时错误，而是会返回错误2042"，您可以通过将查询包装在 IsError 中来测试查找是否失败.

• Use the late-bound version Application.VLookup, which doesn't give you IntelliSense, but instead of throwing a runtime error when the lookup fails, it will return "Error 2042" and you can test whether the lookup failed or not by wrapping it in IsError.

在活动工作表的单元格 A1 中键入 42 .然后在立即窗格中:

Type 42 in cell A1 of the active sheet. Then in the immediate pane:

?iserror(application.VLookup(42,Range("A:B"),1,false))

返回 False

?iserror(application.VLookup(43,Range("A:B"),1,false))

返回 True

?application.WorksheetFunction.VLookup(42,Range("A:B"),1,false)

返回 42

?application.WorksheetFunction.VLookup(43,Range("A:B"),1,false)

引发运行时错误:

该消息最好用"VLookup函数无法在指定的查找范围内找到指定的查找值"之类的措辞来表达.

That message would be better worded as "VLookup function failed to find specified lookup value in specified lookup range", or something like that.

查找失败的原因与任何VLOOKUP可能失败的原因相同:请确认您的 lookup_value 实际上存在于您的 lookup_range 中.注意前导和/或尾随空格，以及文本格式"列.换句话说，假设您想要在查找失败时抛出运行时错误，那是数据问题，而不是代码问题.

The reason your lookup is failing is the same any VLOOKUP might fail for: verify your lookup_value actually exists in your lookup_range. Watch out for leading and/or trailing spaces, and "text-formatted" columns. In other words, assuming you want to throw a runtime error when the lookup fails, it's a data problem, not a code problem.

这篇关于W10上的Excel 2013:不同工作表中的VBA Vlookup产生错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！