问题描述
运行时错误'1004'无法获取WorksheetFunction类的Vlookup属性。
我已经在另一个子例程中有一个vlookup。
这段代码有什么问题吗?我调试,错误出现在那里的策略框。
Sub LinkPolicyNum()
Dim r As Integer
Dim policynum As Variant
Dim lookup_num作为范围
Dim policybox As Variant
r = ActiveCell.Row
'所选单元格的行号
policynum = ActiveSheet.Cells (r,3).Value
Set lookup_num = ThisWorkbook.Sheets(PolicyDetails)。Range(a1:z5000)
policybox = Application.WorksheetFunction.VLookup (policynum,lookup_num,3,False)
'将政策编号与策略详细信息相匹配
MsgBox policynum
MsgBox policybox
End Sub
似乎有你的代码没有错。当您使用 WorksheetFunction
版本的功能时,会发生什么结果,并且不返回任何结果。具体来说,它们会引发错误并中断VBA的执行。在这种情况下,如果您在工作簿中尝试了相同的公式,而不是在VBA中尝试使用相同的公式,则会收到某种形式的错误(#N / A
或
如果你想防止这种情况发生,最简单的方法是改为使用 Application.VLookup
而不是 Application.WorksheetFunction.VLookup
。虽然没有智能感知来帮助这个功能,但除了错误处理之外,它的行为与其他功能相同。如果函数的非 WorksheetFunction
版本有错误,它将返回错误而不是抛出它。这可以让您检查错误,然后继续执行代码。
如果您认为您应该使用 VLOOKUP
这里你可以开始检查文本/数字和其他类似的东西之间的不匹配。我会检查公式,而不是在VBA。
以下是使用其他功能表单并捕获错误的示例。
Sub LinkPolicyNum()
Dim r As Integer
Dim policynum As Variant
Dim lookup_num As Range
Dim policybox As Variant
r = ActiveCell.Row
'所选单元格的行号
policynum = ActiveSheet.Cells(r,3).Value
Set lookup_num = ThisWorkbook.Sheets(PolicyDetails)。Range(a1:z5000)
policybox = Application.VLookup(policynum,lookup_num,3,False)
'将政策编号与策略详细信息匹配
如果IsError(policybox)然后
'可能会使用未找到案例执行某些操作
Else
MsgBox policynum
MsgBox policybox
End If
End Sub
参考在这个问题上:
I run this set of codes and it returns an error:
Run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class.
I already have one vlookup in another sub routine.Is there anything wrong with this code ? I debug and the error appears at the policybox there.
Sub LinkPolicyNum()
Dim r As Integer
Dim policynum As Variant
Dim lookup_num As Range
Dim policybox As Variant
r = ActiveCell.Row
'Row number of the Selected Cell
policynum = ActiveSheet.Cells(r, 3).Value
Set lookup_num = ThisWorkbook.Sheets("PolicyDetails").Range("a1:z5000")
policybox = Application.WorksheetFunction.VLookup(policynum, lookup_num, 3, False)
'to match the policy number to the policy details
MsgBox policynum
MsgBox policybox
End Sub
解决方案 There appears to be nothing wrong with your code. You are seeing the result of what happens when you use the WorksheetFunction
version of functions and no result is returned. Specifically, they throw an error and interrupt execution of the VBA. In this case, if you tried the same formula in the workbook instead of in VBA, you would get some form of error (#N/A
or #VALUE!
possibly).
If you want to prevent this from happening, the easiest thing to do is to change to using Application.VLookup
instead of Application.WorksheetFunction.VLookup
. Although there is no Intellisense to help with this function it behaves the same as the other except for error handling. If the non-WorksheetFunction
version of a function has an error, it will return the error instead of throwing it. This allows you to check for an error and then carry on with your code.
If you think you should be finding a value with VLOOKUP
here then you can start checking for mismatches between text/numbers and other things like that. I would check with formulas and not in VBA though.
Here is an example of using the other functional form and trapping the error.
Sub LinkPolicyNum()
Dim r As Integer
Dim policynum As Variant
Dim lookup_num As Range
Dim policybox As Variant
r = ActiveCell.Row
'Row number of the Selected Cell
policynum = ActiveSheet.Cells(r, 3).Value
Set lookup_num = ThisWorkbook.Sheets("PolicyDetails").Range("a1:z5000")
policybox = Application.VLookup(policynum, lookup_num, 3, False)
'to match the policy number to the policy details
If IsError(policybox) Then
'possibly do something with the "not found" case
Else
MsgBox policynum
MsgBox policybox
End If
End Sub
Reference on this issue: http://dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/
这篇关于无法让vlookup工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!