用VBA字典实现查找两列数据

Question

用VBA字典实现查找两列数据

Answer 1

使用for循环

以前两列为字典变量关键字

进行遍历

然后在后表再次根据关键字匹配，返回值啊

Sub 按钮3_Click()
    Application.ScreenUpdating = False
    Set d = CreateObject("scripting.dictionary")
    Set dd = CreateObject("scripting.dictionary")
    arr = [a1].CurrentRegion
    For j = 2 To UBound(arr)
        dd(arr(j, 1) & "##" & arr(j, 2)) = dd(arr(j, 1) & "##" & arr(j, 2)) + arr(j, 4)
        d(arr(j, 1) & "##" & arr(j, 2)) = d(arr(j, 1) & "##" & arr(j, 2)) + arr(j, 3)
    Next j
    arr = [f1].CurrentRegion
    
    For j = 2 To UBound(arr)
        arr(j, 3) = d(arr(j, 1) & "##" & arr(j, 2))
        arr(j, 4) = dd(arr(j, 1) & "##" & arr(j, 2))
    Next j
    [f1].CurrentRegion = arr
    Application.ScreenUpdating = True
End Sub

Answer 2

Private Sub CommandButton1_Click() '查询
Dim n As Long, m As Long, j As Long, k As Long
Dim t As String, s As String
n = Range("A1").End(xlDown).Row
m = Range("F1").End(xlDown).Row
For j = 2 To m
t = Cells(j, 6).Value
s = Cells(j, 7).Value
For k = 2 To n
If Cells(k, 1).Value = t And Cells(k, 2) = s Then
Cells(j, 8).Value = Cells(k, 3).Value
Cells(j, 9).Value = Cells(k, 4).Value
Exit For
End If
Next
Next

End Sub

追问

一定要用字典和数组