Sub lookuphcpcs()
On Error GoTo errorbox:
Dim hcpcs_code As Long
Dim desc As Variant
hcpcs_code = ActiveCell.Value
If Len(hcpcs_code) > 0 Then
desc = Application.WorksheetFunction.VLookup(Active_cell, 'C:\Users\Username\Desktop\[Fruit Code.xlsx]Sheet1'!$A$2:$B$7, 2, False)
MsgBox "Description for HCPCS Code " & hcpcs_code & " is """ & desc & """"
Else
MsgBox "You did not enter any input!"
End If
Exit Sub
errorbox:
If Err.Number = 1004 Then
MsgBox "No Description found under HCPCS list!"
End If
End Sub
我无法将表数组值放在VBA中的Vlookup下,以指向另一个Excel工作表。
我怎么做?
首先,当Vlookup
您需要处理错误时(例如Vlookup
,找不到匹配项时),可以使用它If Not IsError(Application.VLookup(....
来实现。
其次,在您的情况下,您无需使用On Error GoTo errorbox:
,只需使用Vlookup
我在第一点中编写的错误处理即可。
第三,您可以If Trim(ActiveCell.Value2) <> "" Then
用来验证内部是否有有效的文本或数字,ActiveCell
而不是空白。
第四,应避免使用ActiveCell
,而应使用完全限定的对象。
最后,您要确保"Fruit Code.xlsx"
使用@之前工作簿已打开Vlookup
,如@Tim Williams在上述注释中所建议。
修改后的代码
Option Explicit
Sub lookuphcpcs()
Dim desc As Variant
Dim SourceWb As Workbook
' error trapping in case Fruit Code workbook is closed
On Error Resume Next
Set SourceWb = Workbooks("Fruit Code.xlsx")
On Error GoTo 0
If SourceWb Is Nothing Then
Set SourceWb = Workbooks.Open("C:\Users\Username\Desktop\Fruit Code.xlsx") ' open workbook if it's closed
End If
If Trim(ActiveCell.Value2) <> "" Then ' make sure cell has a string other than space
If Not IsError(Application.VLookup(ActiveCell.Value2, SourceWb.Sheets("Sheet1").Range("A2:B7"), 2, 0)) Then
desc = Application.VLookup(ActiveCell.Value2, SourceWb.Sheets("Sheet1").Range("A2:B7"), 2, 0)
MsgBox "Description for HCPCS Code " & ActiveCell.Value2 & " is """ & desc & """"
Else
MsgBox "No Description found under HCPCS list!"
Exit Sub
End If
Else
MsgBox "You did not enter any input!"
End If
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句