无法让vlookup工作 [英] Unable to get the vlookup to work

问题描述

运行时错误'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 
  

参考在这个问题上： http://dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

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工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！