ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð±Ð¦³ÃöDictionary¥Îªk

[µo°Ý] ½Ð±Ð¦³ÃöDictionary¥Îªk

½Ð±Ð½Ñ¦ì¥ý½ú°ª¤â¡A·Ð½Ð«ü¥X¿ù¦b­þ¸Ì¡H ¨Æ¥óÅX°Ê-°Ó«~µn°Oªí-Test1.zip (17.72 KB) ¤£ª¾­þ¥X¿ù¡H¨S¦³¿ìªk°õ¦æ¡I

Dim Km As Object, Arr()
Private Sub Worksheet_Activate()
    Dim i%
    Set Km = CreateObject("Scripting.Dictionary")
    Arr = Range("j3:m" & Range("j" & Rows.Count).End(3).Row).Value
    For i = 1 To UBound(Arr)
        Km(Arr(i, 1)) = i   '§â¥N½X©M¹ïÀ³ªº§Ç¸¹¸Ë¤J¦r¨å
    Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B3:B126")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
'        Target.Value = Cells(Rows.Count, "B").End(xlUp).Offset(1, 1)
        Target.Offset(0, -1).Value = Date
        For i = 1 To 3
            Target.Offset(0, i).Value = Arr(Km(Target.Value), i + 1)
        Next
'        Target.Resize(1, 3).Value = Arr(Km(Target.Value).Items)
    Application.EnableEvents = True

End Sub
[attach]30373
Lin

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD