Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim d, wb As Workbook, ar

Set d = CreateObject("Scripting.Dictionary")

Set wb = GetObject(ThisWorkbook.Path & "\Book1.xlsx")

Application.ScreenUpdating = False

ar = wb.Sheets(1).Range("A1").CurrentRegion

wb.Close 0

For i = 2 To UBound(ar)

d(ar(i, 1)) = Array(ar(i, 2), ar(i, 3))

Next

For i = 2 To [b65536].End(3).Row

Cells(i, 3).Resize(1, 2) = d(Cells(i, 2).Value)

Next

Application.ScreenUpdating = True

End Sub

輸入單元格觸發(fā)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

d(ar(i, 1)) = Array(ar(i, 2), ar(i, 3))

d(ar(i, 1)) = Array(ar(i, 2), ar(i, 3)) ? → d(ar(i, 1)) = Array(ar(i, 2), ar(i, 2)

d(ar(i, 1)) = Array(ar(i, 2), ar(i, 3))? → d(ar(i, 1)) = Array(ar(i,1), ar(i, 3))

Cells(i, 3).Resize(1, 2) = d(Cells(i, 2).Value) → Cells(i, 3).Resize(1, 2) = d(Cells(i, 1).Value)

Cells(i, 3).Resize(1, 2) = d(Cells(i,2).Value) → Cells(i, 3).Resize(1, 3) = d(Cells(i,2).Value)

Cells(i, 3).Resize(1, 2) = d(Cells(i, 2).Value) → Cells(i, 3).Resize(2, 2) = d(Cells(i, 2).Value)

Cells(i, 3).Resize(1, 2) = d(Cells(i, 2).Value) → Cells(i, 4).Resize(1, 2) = d(Cells(i, 2).Value)