excel - 跳过函数错误并继续下一次检查 (VLOOKUP)

Question

我正在运行一个基本的 VLOOKUP 来获取员工的电子邮件地址。

但是，当查找值输入数组中不存在的员工姓名时，会引发错误。

我添加了一个“On Error Resume Next”，但这只是导致剩余的电子邮件地址成为最后遇到的电子邮件地址，而不是继续搜索并将当前员工用作搜索变量。

我想要的是当搜索变量 ProjectManName数组中不存在，跳过该行，然后正常继续搜索。然后我会去手动填写空的电子邮件地址。

Dim myLookupValue As Range
Dim strResult As String
Dim lngLastRow As Long
Dim lngLoop As Long
Dim ProjectManName As String

Set myLookupValue = Worksheets("Employees").Range("A1", Worksheets("Employees").Range("B1").End(xlDown))

With Worksheets("Project Summary")

    lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    For lngLoop = 2 To lngLastRow
        On Error Resume Next
       ProjectManName = .Cells(lngLoop, 1).Value
       strResult = Application.WorksheetFunction.VLookup(ProjectManName, myLookupValue, 2, False)
       Range("K" & lngLoop).Value = strResult
       ActiveCell.Offset(1, 0).Select
    Next

End With

另外，对于 myLookupValue，我是否需要 Worksheets("Employees") 的第二个实例？当我没有这样做时，它也给出了一个错误，因为范围中的第二个单元格查看的是当前工作表而不是员工工作表。

最后，如何替换 Range("K" & lngLoop).Value 中的“K”成为第一个空列？

Answer 1

when the lookup value enters an employees name that doesn't exist in the array, it throws an error.

这完全是设计使然。 Application.WorksheetFunction函数是早期绑定(bind)的并引发错误而不是返回它们，这完全是惯用的 VB 行为。

看起来您想要“Excel 工作表”行为，其中出错的工作表函数将返回 Variant/Error单元格显示为 #N/A 的值: 那 Variant/Error值(value)使 IsError返回 True , 并且只能合法地与其他错误值进行比较，例如 CVErr(xlErrNa) .

像许多 COM 类型一样， Excel.Application接口(interface)是可扩展的，这意味着可以在运行时添加成员。事实证明， WorksheetFunction 的成员有效地扩展了它。接口(interface)，所以 Application.VLookup不仅编译得非常好(与 Application.AnythingWhatsoever 一样)，它还是一个后期绑定(bind)的实现，其行为与工作表单元格调用时的工作表函数完全相同:它返回一个 Variant/Error值而不是引发标准的惯用运行时错误...假设您正确设置了所有参数(后期绑定(bind)调用不会获得 IntelliSense/自动完成)，因为如果您打错字( Option Explicit 无法保存你)或参数错误，预计会引发错误 438 或 1004。

但是您无法捕获 String 中的返回值- 当查找产生 Error 时，这将是类型不匹配错误值(您不能将该类型强制转换为 Variant 以外的任何内容)。

Dim lookupResult As Variant
lookupResult = Application.VLookup(ProjectManName, myLookupValue, 2, False)
If Not IsError(lookupResult) Then
    strResult = CStr(lookupResult)
    '...
''Else
''    'lookup failed
End If

也就是说，通常应该首选早期绑定(bind)版本，仅适用于 IntelliSense。 On Error Resume Next正确使用在这里会有所帮助 - 只需将查找拉入其自己的范围:

For lngLoop = 2 To lngLastRow
    ProjectManName = .Cells(lngLoop, 1).Value
    [ActiveSheet.]Range("K" & lngLoop).Value = GetProjectManager(ProjectManName)
    'ActiveCell.Offset(1, 0).Select '<~ why?
Next

Private Function GetProjectManager(ByVal name As String) As String
     Dim source As Range
     With Worksheets("Employees")
         On Error Resume Next
         GetProjectManager = Application.WorksheetFunction.VLookup(name, .Range("A1", .Range("B1").End(xlDown)), 2, False)
         On Error GoTo 0
     End With
End Function

至于 myLookupValue (坏名:应该是 myLookupRange 或 lookupSource 或 lookupTable - “查找值”通常被理解/读取为您正在寻找的值) - 您绝对需要引用 Employees表(不合格的 Range 调用是错误 1004 的一个很好的方法) - 这并不意味着您需要从 Worksheets 中取消引用该对象两次收集 - 如上所示...请注意，通过将查找移动到它自己的范围内，我们还消除了调用者甚至需要关心查找源表的需要。