vba “无法获取 WorksheetFunction 类的 VLookup 属性”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19280477/
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 get the VLookup property of the WorksheetFunction Class" error
提问by user2864307
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
Worksheet 3 is from which the information is being drawn (the vendor number and name).
工作表 3 是从中提取信息的（供应商编号和名称）。
When I go back to the form to test the code, I get the following error:
当我返回表单测试代码时，出现以下错误：
Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class
运行时错误“1004”：无法获取 WorksheetFunction 类的 VLookup 属性
How do I fix this?
我该如何解决？
回答by Santosh
Try below code
试试下面的代码
I will recommend to use error handler while using vlookup because error might occur when the lookup_value is not found.
我会建议在使用 vlookup 时使用错误处理程序，因为当找不到 lookup_value 时可能会发生错误。
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
OR
或者
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
回答by Omar Khan
I was having the same problem. It seems that passing Me.ComboBox1.Valueas an argument for the Vlookupfunction is causing the issue. What I did was assign this value to a double and then put it into the Vlookup function.
我遇到了同样的问题。似乎Me.ComboBox1.Value作为Vlookup函数的参数传递导致了这个问题。我所做的是将此值分配给一个双精度值，然后将其放入 Vlookup 函数中。
Dim x As Double
x = Me.ComboBox1.Value
Me.TextBox1.Value = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet3").Range("Names"), 2, False)
Or, for a shorter method, you can just convert the type within the Vlookup function using Cdbl(<Value>).
或者，对于较短的方法，您可以使用Cdbl(<Value>).
So it would end up being
所以它最终会成为
Me.TextBox1.Value = Application.WorksheetFunction.VLookup(Cdbl(Me.ComboBox1.Value), Worksheets("Sheet3").Range("Names"), 2, False)
Strange as it may sound, it works for me.
虽然听起来很奇怪，但它对我有用。
Hope this helps.
希望这可以帮助。
回答by mjpowers0903
I was just having this issue with my own program. I turned out that the value I was searching for was not in my reference table. I fixed my reference table, and then the error went away.
我只是在我自己的程序中遇到了这个问题。我发现我要搜索的值不在我的参考表中。我修复了我的参考表，然后错误消失了。
