[µo°Ý] ¨Æ¥ó¦Û°Ê±a¥X¡]¤G­ÓÄæ¦ì¡^

s7659109

°ÝÃD¡Gb&cÄækey§¹«á,¦Û°Ê±a¥Xf&gÄæ

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
¥Ø«e¥u·|¤@Äæ¡A¤GÄæµo²{¥dÃö¡A¨D¸Ñ

s7659109

·í¥u¦³bÄæ¡AµLcÄæ¡A±a¤£¥X¸ê®Æ

­ã´£³¡ªL

¸ê®Æ¤£¦hªº¸Ü, ¥Î¤½¦¡:
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

F2/¤½¦¡:
=LOOKUP(,0/(name!$B$2:$B$999=$B2)/(name!$F$2:$F$999=$C2),name!G$2:G$999)

TEST0705-v1.rar (14.66 KB)

¤U¸ü¦¸¼Æ: 8

2019-7-6 11:57

=====================================
¸ê®Æ¦h, ¨CKEY¤@¦¸´N¶]¤@¦¸°j°é, ¤]¤£¬O¦n¿ìªk~~