|
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Column <> 1 Then Exit Sub
- If Target.Count > 1 Then Exit Sub
- Dim d, arr, s$, i&
- Set d = CreateObject("Scripting.Dictionary")
- arr = Sheets("Sheet1").UsedRange.Value
- For i = 2 To UBound(arr)
- If arr(i, 2) = Target.Value Then
- If Not d.exists(arr(i, 2)) Then
- d(arr(i, 2)) = ""
- s = arr(i, 1)
- Else
- s = s & "," & arr(i, 1)
- End If
- End If
- Next
- Target.Offset(, 1) = s
- Set d = Nothing
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Column <> 1 Then Exit Sub
- Dim d, arr, i
- Set d = CreateObject("Scripting.Dictionary")
- arr = Sheets("Sheet1").UsedRange.Value
- For i = 2 To UBound(arr)
- If arr(i, 2) <> "" Then d(arr(i, 2)) = ""
- Next
- With Target.Validation
- .Delete
- .Add 3, 1, 1, Join(d.keys, ",")
- End With
- Set d = Nothing
- End Sub
复制代码
|
|