vba - 当项目位于列表中时，Vlookup 返回错误

Question

问题:项目位于 VLOOKUP 表中，但在相应的单元格中返回“缺失”值。当 conUD 打印时，如果我从调试窗口复制粘贴它并按 Ctrl+F - 在 J 列上找到它，它会毫无问题地找到它。为什么Ctrl+F可以找到而不是VLookup？

注释:

• 这只是相关代码，而不是整个 block 。
• lrVelocity 计算正确。值为 1,951。
• 这些值的格式为 0001HCM8889W01，因此不会违反 VLOOKUP 最大字符数限制。
• 如您所见，我尝试修剪任何不可见的空格并确保它们都是字符串。
• 我对 VBA 相当陌生，并且感谢有关此问题的所有帮助。我阅读了多篇 Google 文章，但这些修复都没有解决我的问题。

Option Explicit
Dim wsMain As Worksheet
Dim wsQuantity As Worksheet
Dim wsVelocity As Worksheet
Dim wsParameters As Worksheet
Dim wsData As Worksheet
Dim lrMain As Long 'lr = last row
Dim lrQuantity As Long
Dim lrVelocity As Long
Dim lrParameters As Long
Dim lrData As Long
Dim conUD As String 'con=concatenate
Dim conECD As String
Dim calcWeek As Long
Dim RC As Long 'Row Counter
Dim vl As Variant 'Vlookup, Variant to allow for errors without breaking the code

calcWeek = wsParameters.Range("B3").Value
lrVelocity = wsVelocity.Cells.Find(What:="*", SearchOrder:=xlByRows, Searchdirection:=xlPrevious).Row
Set wsMain = Worksheets("Main Tab")
Set wsVelocity = Worksheets("Velocity")

 For RC = 2 To 10 'lrVelocity
With wsVelocity
    .Cells(RC, 10) = .Cells(RC, 1) & .Cells(RC, 1) & .Cells(RC, 4) & .Cells(RC, 5) & .Cells(RC, 9)
    .Cells(RC, 10).Value = CStr(Trim(.Cells(RC, 10).Value))
    .Cells(RC, 11) = .Cells(RC, 6)
    .Cells(RC, 12) = .Cells(RC, 7)
    .Cells(RC, 13) = .Cells(RC, 8)
    .Cells(RC, 14) = .Cells(RC, 3)
    .Cells(RC, 22) = .Cells(RC, 1) & .Cells(RC, 9)
End With
Next RC

For RC=2 To 10
conUD = wsMain.Cells(RC, 21) & wsMain.Cells(RC, 4) & calcWeek
conUD = CStr(Trim(conUD))
Debug.Print conUD
wsVelocity.Activate
vl = Application.VLookup(conUD, wsVelocity.Range(wsVelocity.Cells(2, 10), wsVelocity.Cells(lrVelocity, 11)), 2, False)
If IsError(vl) Then
    wsMain.Cells(RC, 10).Value = "Missing"
Else
    wsMain.Cells(RC, 10).Value = vl
End If
Next RC

Answer 1

我认为@user1274820 说得有道理。通常我们会像您一样使用 Application.Vlookup ，并且预计可能不会在 table_array 的第一列中找到该值，而您想要处理该问题输出中存在“缺失”值。

但是，如果找到值，但返回列中的值(k，在您的情况下)是错误，则该函数将返回错误以及。在您的例子中，虽然在 J 列中找到了值，但 K 列似乎包含 #N/A。 (如果情况并非如此，请告诉我!)

Application.Vlookup 在这两种情况下都会返回 Error 2042:

1. 在table_array的第一列中找不到lookup_value(最常见的用法和期望，IMO)
2. 已找到 lookup_value，但 col_index_num 的结果值本身就是一个错误。

因此，如果即使查找值存在，返回值也可能包含错误，那么我们就无法使用 Application.Vlookup 来测试该值是否存在，但您可以使用替代方法，例如 WorksheetFunction.CountIf 或 Application.Match。

在这里，我们只需查询 J 列并使用 CountIf 来确保至少有 1 个匹配值。这将提前验证我们的Vlookup，但我们仍然需要处理返回值中可能出现的错误。

For RC = 2 to 10
    conUD = wsMain.Cells(RC, 21) & wsMain.Cells(RC, 4) & calcWeek
    conUD = CStr(Trim(conUD))
    Debug.Print conUD
    With wsVelocity
        Dim lookupRange as Range
        Set lookupRange = .Range(.Cells(2, 10), .Cells(lrVelocity, 11))
    End With
    If Application.WorksheetFunction.CountIf(lookupRange.Columns(1), conUD) <> 0 Then
        'The value is found, it should be safe to use VLOOKUP 
        vl = Application.VLookup(conUD, lookupRange, 2, False)
        '## Handles an error in the return value from the return column
        If IsError(vl) Then
            '## Copies the error from return column, or modify as needed
            wsMain.Cells(RC, 10).Value = CVerr(vl)
        Else
            '## Value found in Col J and return Vlookup from Col K
            wsMain.Cells(RC, 10).Value = vl
        End If
    Else
        '## Value NOT FOUND in column J
        wsMain.Cells(RC, 10).Value = "Missing"
    End If
Next

更新

从聊天中，我可以看到您的主表和查找表值的格式不同。在查找表中，您复制了一个前缀，例如“0001HCM8889”，因此最终得到“0001HCM8890001HCM889W01”。

这就是为什么 Find 或 Ctrl+F 可以找到单元格，但 VLOOKUP 却不能，因为它需要精确匹配。

正如您在第一个循环中构建/清理查找表一样，您应该能够通过执行以下操作来修复它:

For RC = 2 To 10 'lrVelocity

    With wsVelocity
        '## Removed the duplicate .Cells(RC, 1) from the next line ##
        .Cells(RC, 10) = .Cells(RC, 1) & .Cells(RC, 4) & .Cells(RC, 5) & .Cells(RC, 9)
        .Cells(RC, 10).Value = CStr(Trim(.Cells(RC, 10).Value))
        .Cells(RC, 11) = .Cells(RC, 6)
        .Cells(RC, 12) = .Cells(RC, 7)
        .Cells(RC, 13) = .Cells(RC, 8)
        .Cells(RC, 14) = .Cells(RC, 3)
        .Cells(RC, 22) = .Cells(RC, 1) & .Cells(RC, 9)
    End With

Next RC