跳过功能错误并继续进行下一个检查(VLOOKUP) [英] Skip over function error and continue with next check (VLOOKUP)

问题描述

我正在运行基本的VLOOKUP以获取员工的电子邮件地址.

I am running a basic VLOOKUP to get an employees email address.

但是，当查询值输入数组中不存在的雇员名称时，则会引发错误.

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

我添加了一个"On Error Resume Next"，但这只是使其余电子邮件地址成为遇到的最后一个电子邮件地址，而不是继续搜索并将当前雇员用作搜索变量.

I added an "On Error Resume Next" but this just caused the remainder of the email addresses to be the last email address encountered, instead of continuing the search and using the current employee as the search variable.

我想要的是当搜索变量 ProjectManName 在数组中不存在时，只需跳过该行，然后照常继续搜索.然后，我将手动填写空白电子邮件地址.

What I want is when the search variable ProjectManName doesn't exist in the array, to just skip over that line, and continue the search as normal. I will then go and fill in the empty email addresses manually.

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")的第二个实例?当我不这样做时，还会出现一个错误，因为该范围内的第二个单元格查看的是当前工作表，而不是雇员的工作表.

Also, for the myLookupValue, do I need the second instance of Worksheets("Employees")? When I didn't do this it also gave an error because the second cell in the range looked at the current worksheet instead of the Employees worksheet.

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

Lastly, how can I replace "K" in Range("K" & lngLoop).Value to be the first empty column?

推荐答案

当查找值输入数组中不存在的雇员名称时，会引发错误.

这完全是设计使然. Application.WorksheetFunction 函数是早期绑定错误，并且是 raise 错误，而不是返回错误，这完全是VB惯用的行为.

That's entirely by design. Application.WorksheetFunction functions are early-bound and raise errors instead of returning them, which is completely idiomatic VB behavior.

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

Looks like you want the "Excel worksheet" behavior, where a worksheet function that errors out will return a Variant/Error value that the cell displays as #N/A: that Variant/Error value makes IsError return True, and can only be legally compared to other error values, e.g. CVErr(xlErrNa).

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

Like many COM types, the Excel.Application interface is extensible, meaning members can be added to it at run-time. Turns out, it's effectively extended with members of the WorksheetFunction interface, so Application.VLookup not only compiles perfectly fine (as does Application.AnythingWhatsoever), it's a late-bound implementation that behaves exactly like the worksheet function does when invoked by a worksheet cell: it returns a Variant/Error value rather than raising a standard, idiomatic run-time error... assuming you get all the parameters right (late-bound calls don't get IntelliSense/autocomplete), because if you make a typo (Option Explicit can't save you) or get the parameters wrong, expect error 438 or 1004 to be raised.

但是您不能在 String 中捕获返回值-当查询产生 Error 类型不匹配错误>值(除了 Variant 外，您不能将其强制转换为其他任何类型).

But you can't capture the return value in a String - that'll be a type mismatch error when the lookup yields an Error value (you can't coerce that type into anything other than a 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

那是说早期绑定版本通常应该是首选，因为它仅用于 IntelliSense .<错误恢复下一个错误 正确使用在这里可能会有所帮助-只需将查找放入其自己的范围即可:

That said the early-bound version should generally be preferred, be it only for IntelliSense. On Error Resume Next used correctly can be helpful here - simply pull the lookup into its own scope:

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 集合中取消引用该对象-如上所示...请注意通过将查找移到其自己的范围内，我们还消除了调用者甚至不需要关心查找源表的需求.

As for myLookupValue (bad name: should be myLookupRange or lookupSource or lookupTable - "lookup value" is generally understood/read as being the value you're looking for) - you absolutely do need a reference to the Employees sheet (unqualified Range calls are a very good recipe for error 1004) - that doesn't mean you need to dereference that object from the Worksheets collection twice - as shown above... note that by moving the lookup into its own scope, we also remove the need for the caller to even need to care about the lookup source table.

这篇关于跳过功能错误并继续进行下一个检查(VLOOKUP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！