Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B2:C65536").Address <> Union(Target, Range("B2:C65536")).Address Then Exit Sub
Dim arr, brr(), i&, d As Object
Set d = CreateObject("scripting.dictionary")
arr = Sheets("name").UsedRange
For i = 2 To UBound(arr)
d(arr(i, 2) & Mid(arr(i, 4), 6, 1) & arr(i, 6)) = Array(arr(i, 7), arr(i, 8))
Next
arr = Cells(Target.Row, 2).Resize(Target.Rows.Count, 3)
ReDim brr(1 To UBound(arr), 1 To 2)
For i = 1 To UBound(arr)
brr(i, 1) = d(arr(i, 1) & arr(i, 2))(0)
brr(i, 2) = d(arr(i, 1) & arr(i, 2))(1)
Next
l = d.keys
m = d.items
Cells(Target.Row, 6).Resize(i - 1, 2) = brr
End Sub
目前只會一欄，二欄發現卡關，求解作者: 准提部林 時間: 2019-7-6 11:58
資料不多的話, 用公式:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xA As Range, xB As Range
With Target
If .Count > 1 Or .Row = 1 Or .Column < 2 Or .Column > 3 Then Exit Sub
Set xA = .Cells(1, IIf(.Column = 2, 1, 0)): Set xB = xA(1, 2)
With Range(xA(1, 5), xA(1, 6))
If xA = "" Or xB = "" Then .ClearContents
.Formula = "=LOOKUP(,0/(name!$B$2:$B$999=" & xA.Address(0, 1) & ")/(name!$F$2:$F$999=" & xB.Address(0, 1) & "),name!G$2:G$999)"
.Value = .Value
.Replace "#N/A", "", Lookat:=xlWhole
End With
End With
End Sub
