|
发表于 2012-1-5 21:42
|
显示全部楼层
本楼为最佳答案
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim c As Range
- If Target.Address = "$C$3" Then
- With Sheets("数据库")
- Set c = .Range("d:d").Find(Target, , , 1)
- If c Is Nothing Then
- .Range("d65536").End(3).Offset(1) = Target
- End If
- End With
- End If
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dim arr
- Dim dic
- Set dic = CreateObject("scripting.dictionary")
- If Target.Address = "$C$4" Then
- With Sheets("数据库")
- arr = .Range("D1:D" & .Range("d65536").End(xlUp).Row)
- For i = 1 To UBound(arr)
- dic(arr(i, 1)) = ""
- Next
- End With
- arrstr = Join(dic.keys, ",")
- With Sheet1.Range("C3").Validation
- .Delete
- .Add Type:=xlValidateList, Formula1:=arrstr
- .IgnoreBlank = True
- .ShowError = False
- End With
- End If
- End Sub
复制代码 |
|