Excel 用户表单 VBA VLOOKUP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41765218/
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
Excel Userform VBA VLOOKUP
提问by Abigal
I am creating a userform where based on a drop down list of items called "ContractsList" I would like a Vlookup formula to return a text data in "TextBox 1".
我正在创建一个用户表单，其中基于名为“ContractsList”的项目下拉列表，我想要一个 Vlookup 公式来返回“TextBox 1”中的文本数据。
I get an error message saying "Run-time error'1004': Unable to get the Vlookup property of the worksheetfunction class
我收到一条错误消息，指出“运行时错误‘1004’：无法获取工作表函数类的 Vlookup 属性
Not sure what I am doing wrong, here is my code if anyone can spot the error.
不知道我做错了什么，如果有人能发现错误，这是我的代码。
Private Sub ContractsList_AfterUpdate()
If WorksheetFunction.CountIf(Sheet2.Range("A:A"),Me.ContractsList.Value) = 0 Then
MsgBox "This contract is not on the list"
Me.ContractsList.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
.TextBox1 = Application.WorksheetFunction.VLookup(Me.TextBox1, ("B5:B72"), 2, 0)
End With
End Sub
采纳答案by Abigal
Finally got it to work as below:
最后让它工作如下：
Private Sub ContractsList_AfterUpdate()
If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.ContractsList.Value) = 0 Then
MsgBox "This contract is not on the list"
Me.ContractsList.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
.TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Sheet2.Range("A5:E72"), 2, 0)
End With
End Sub
I just needed to add "Sheet2.Range("A5:E75")
我只需要添加 "Sheet2.Range("A5:E75")
Thank you all for your help.
谢谢大家的帮助。
