excel - 它说它“无法获取 WorksheetFunction 类的 vlookup 属性”
问题描述
我在 excel VBA 中遇到问题。它说它是unable to get the vlookup property of the WorksheetFunction class
Private Sub cmb_Product_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Product_Master")
If Me.cmb_Product.Value = "" Or Me.cmb_Type.Value = "" Then Me.txt_Rate.Value = ""
emphasized text[enter image description here][1]
If Me.cmb_Type.Value = "Sale" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.cmb_Product, sh.Range("B:D"), 2, 0)
ElseIf Me.cmb_Type.Value = "Purchase" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.cmb_Product, sh.Range("B:D"), 3, 0)
End If
End Sub
解决方案
WorksheetFunction.VLookup
如果找不到查找值,这是正常行为。
您有两种可能性:
(a)忽略错误
Dim rate As Double ' Or String, not sure what your data is.
On Error Resume Next
rate = WorksheetFunction.VLookup(Me.cmb_Product, sh.Range("B:D"), 2, false)
On Error Goto 0
Me.txt_Rate.Value = rate
如果没有找到您的产品,价格将为空
(b)Application.VLookup
改为使用。这不会引发运行时错误,但如果未找到匹配项,它将返回错误作为结果。请注意,您需要将结果声明为 Variant,否则您将收到类型不匹配错误(错误 13)。
Dim rate As Variant
rate = Application.VLookup(Me.cmb_Product, sh.Range("B:D"), 2, false)
if not isError(rate) then Me.txt_Rate.Value = rate
更新:有关更多详细信息,请参阅本文:https ://rubberduckvba.wordpress.com/2021/02/15/worksheetfunction-and-errors/
推荐阅读
- scala - 任务不可序列化:Spark
- javascript - jQuery-ui resizable:拖动元素时调整大小的问题
- exception - 使用循环变量时,Raku 是否会丢弃异常?
- continuous-integration - 如何将 Liquibase 集成到大型组织的现有 CI/CD 管道中?
- labview - 如何在LabVIEW中将簇写入文件?
- python - 通过 django 访问 Moodle 网络服务
- c++ - Maximum and minimum values not printing in main () function
- qt - QML - RowLayout - 甚至没有间距
- dialogflow-es - 如何限制聊天机器人 Kommunicate 中收到的聊天数量
- java - Maven HTTPS 迁移抛出 501