避免使用"#value"带有加密字典的Vlookup时出现错误 [英] Avoid the "#value" error when using a Vlookup with Scrypting Dictionary

问题描述

我正在尝试使用在下面的问题中找到的VBA Vlookup，但是我一直得到结果#Value(根据评论，我不是唯一的一个).
问题:如何优化vlookup以提高搜索量? (替代VLOOKUP)

I am trying to use a VBA Vlookup that I found in the Question below but I keep getting the result #Value (and I am not the only one according to the comments).
Question: How to optimize vlookup for high search count ? (alternatives to VLOOKUP)

将功能放入VBA之后.我像正常的Vlookup一样在工作表中使用它:"=vbalookup(value,Range,Col)". 我也尝试过作为数组公式，但是它仍然不起作用.

After putting the Function in VBA. I am using it in my sheet like a normal Vlookup: "=vbalookup(value,Range,Col)". I also tried as an array formula but it still doesn't work.

有人看到为什么吗?

Function vbalookup(lookupRange As Range, refRange As Range, dataCol As Long) As Variant
    Dim dict As New Scripting.Dictionary
    Dim myRow As Range
    Dim I As Long, J As Long
    Dim vResults() As Variant

    ' 1. Build a dictionnary
    For Each myRow In refRange.Columns(1).Cells
        ' Append A : B to dictionnary
        dict.Add myRow.Value, myRow.Offset(0, dataCol - 1).Value
    Next myRow

    ' 2. Use it over all lookup data
    ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
    For I = 1 To lookupRange.Rows.Count
        For J = 1 To lookupRange.Columns.Count
          If dict.Exists(lookupRange.Cells(I, J).Value) Then
            vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
          End If
        Next J
    Next I

    vbalookup = vResults
End Function

推荐答案

我强烈建议您首先学习代码的功能，而不是盲目地从别人那里复制代码.如果必须盲目复制，请先阅读注释:

Instead of blindly copying code from someone else, I highly suggest first learning what the code is doing. If you must blindly copy, read the comments first: How to optimize vlookup for high search count ? (alternatives to VLOOKUP)

对代码问题(除了设计，语法，缩进等问题)采取措施，就是您的函数未返回特定值:

Taking a stab at the problem with the code (beyond issues with design, syntax, indentation, etc) is that your function isn't returning a specific value:

    ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
    For I = 1 To lookupRange.Rows.Count
        For J = 1 To lookupRange.Columns.Count
          If dict.Exists(lookupRange.Cells(I, J).Value) Then
            vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
          End If
        Next J
    Next I

    vbalookup = vResults

您看到它如何创建2d数组，并根据公式返回此数组吗?尝试在函数结束之前添加断点，然后查看Locals窗口中的vResults.

Do you see how it is creating a 2d array, and returns this array as a result of the formula? Try adding a breakpoint before the end of the function, and take a look at what vResults is in the Locals window.

这意味着，要实际使用该功能，必须将作为数组公式输入到单元格中.

This means that, in order to actually use the function, it must be entered in the cell as an array formula.

要简单地执行此操作，在将公式输入到单元格中时，按CTRL+SHIFT+ENTER而不是ENTER即可.

To do this simply, press CTRL+SHIFT+ENTER instead of ENTER when entering the formula into the cell.

但是要特别小心:不仅您不仅不知道此代码的工作方式，而且还可能不知道数组公式的工作方式. 您和您一个人负责计算.如果您正在做任何重要的事情，那么这里是小龙，然后转向相反的方向.

This comes with a strong caution though: not only do you already not know how this code is working, but you also likely don't know how array formulas work. You, and you alone are responsible for your calculations. If you are working on anything important whatsoever, then here be dragons and turn the other way.

一旦您了解了这一切的工作原理，您可能会更轻松地使用代码.

Once you get to the point of understanding how this all works, you may have an easier time using the code.

这篇关于避免使用"#value"带有加密字典的Vlookup时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！