vba - 在 VBA 宏中使用字典的简单 VLOOKUP

Question

我希望通过 VBA 宏中的字典进行 vlookup。我在互联网上看到了一些示例，但它们大多非常具体，我希望获得更多“基本”代码的帮助。我将使用一个简单的示例来说明我想要实现的目标:

查找值是从“订单”工作表上的单元格 B2 开始的动态范围内的每个单元格(底行不同)

表格数组位于从单元格 E2 开始并延伸到“报告”工作表上的 L 列的动态范围内(底行不同)

列索引号为 8(L 列)

范围查找为 False

我当前的代码如下:

Sub DictionaryVLookup()
Dim x, y, z(1 To 10)
Dim i As Long
Dim dict As Object
Dim LastRow As Long

LastRow = Worksheets("Report").Range("B" & Rows.Count).End(xlUp).Row


x = Sheets("Orders").Range("B2:B" & LastRow).Value
y = Sheets("Report").Range("E2:E" & LastRow).Value    'looks up to this range
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1)) = x(i, 1)
Next i

For i = 1 To UBound(y, 1)
    If dict.exists(y(i, 1)) Then
        z(i) = y(i, 1)
    Else
        z(i) = "NA"
    End If
Next i
Worksheets("Orders").Range("Z2:Z" & LastRow).Value = Application.Transpose(z)    'this is where the values are placed

End Sub

我似乎缺少“查找”部分，目前它运行没有错误，并且简单地放置了查找“找到”的值，但我不知道如何使返回的值偏移(想要返回列 L在这个例子中)。

我还用这段代码做了一些“弗兰肯斯坦”的工作——所以我不确定为什么会出现这种情况:

   Dim x, y, z(1 To 10)

(1到10)我想我会想动态的。

这是我第一次尝试以这种方式使用字典 - 希望通过这个简单的示例获得基本的理解，然后我可以将其实现到更多涉及的情况中。

我知道还有其他方法可以做我所描述的事情，但希望专门了解字典。

提前感谢您的帮助!

Answer 1

像这样的东西:

Sub DictionaryVLookup()

    Dim x, x2, y, y2()
    Dim i As Long
    Dim dict As Object
    Dim LastRow As Long, shtOrders As Worksheet, shtReport As Worksheet

    Set shtOrders = Worksheets("Orders")
    Set shtReport = Worksheets("Report")
    Set dict = CreateObject("Scripting.Dictionary")

    'get the lookup dictionary from Report
    With shtReport
        LastRow = .Range("E" & Rows.Count).End(xlUp).Row
        x = .Range("E2:E" & LastRow).Value
        x2 = .Range("L2:L" & LastRow).Value
        For i = 1 To UBound(x, 1)
            dict.Item(x(i, 1)) = x2(i, 1)
        Next i
    End With

    'map the values
    With shtOrders
        LastRow = .Range("B" & Rows.Count).End(xlUp).Row
        y = .Range("B2:B" & LastRow).Value    'looks up to this range
        ReDim y2(1 To UBound(y, 1), 1 To 1)   '<< size the output array
        For i = 1 To UBound(y, 1)
            If dict.exists(y(i, 1)) Then
                y2(i, 1) = dict(y(i, 1))
            Else
                y2(i, 1) = "NA"
            End If
        Next i
        .Range("Z2:Z" & LastRow).Value = y2  '<< place the output on the sheet
    End With

End Sub