“无法获取WorksheetFunction类的VLookup属性”错误 [英] "Unable to get the VLookup property of the WorksheetFunction Class" error

问题描述

我正在尝试开发一种表单来跟踪发票的进度。表单将有一个组合框，我可以点击并选择一个供应商号码。我希望文本框根据从组合框中选择的供应商号码自动填写。这是我迄今为止所做的：

I am trying to develop a form to track invoices as they come in. The form will have a combobox where I can click on and select a vendor number. I want the textbox to automatically fill in based on the vendor number selected from the combobox. Here's what I have so far:

Private Sub ComboBox1_Change()    
    'Vlookup when ComboBox1 is filled
    Me.TextBox1.Value = Application.WorksheetFunction.VLookup( _
        Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False)    
End Sub

工作表3是从中绘制信息（供应商编号和名称）。

Worksheet 3 is from which the information is being drawn (the vendor number and name).

当我回到表单测试代码时，我收到以下错误：

When I go back to the form to test the code, I get the following error:

运行时错误'1004'：无法获取WorksheetFunction类的VLookup属性

Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class

如何修复这个？

推荐答案

尝试下面的代码

我会推荐使用错误处理程序使用vlookup，因为没有找到lookup_value时可能会发生错误。

I will recommend to use error handler while using vlookup because error might occur when the lookup_value is not found.

Private Sub ComboBox1_Change()


    On Error Resume Next
    Ret = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False)
    On Error GoTo 0

    If Ret <> "" Then MsgBox Ret


End Sub

或

 On Error Resume Next

    Result = Application.VLookup(Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False)

    If Result = "Error 2042" Then
        'nothing found
    ElseIf cell <> Result Then
        MsgBox cell.Value
    End If

    On Error GoTo 0

这篇关于“无法获取WorksheetFunction类的VLookup属性”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！