|
楼主 |
发表于 2011-8-13 11:27
|
显示全部楼层
回复 无聊的疯子 的帖子
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icell As Range
If Target.Address <> "$E$4" And Target.Address <> "$G$10" And Target.Address <> "$I$7" Then End
If Target.Address = "$E$4" Then
Set icell = Target.Offset(1)
ElseIf Target.Address = "$G$10" Then
Set icell = Target.Offset(3)
ElseIf Target.Address = "$I$7" Then
Set icell = Target.Offset(, 2)
End If
If Target = "" Then
With icell
.Validation.Delete
.Value = ""
End With
End
End If
Dim d As Object, arr, i%
Set d = CreateObject("scripting.dictionary")
With Sheets("总人数")
icolumn = .Rows(5).Find(Target).Column
irow = .[D65536].End(3).Row
irow = .[E65536].End(3).Row
irow = .[F65536].End(3).Row
irow = .[G65536].End(3).Row
irow = .[H65536].End(3).Row
irow = .[I65536].End(3).Row
irow = .[J65536].End(3).Row
irow = .[K65536].End(3).Row
irow = .[L65536].End(3).Row
irow = .[M65536].End(3).Row
irow = .[N65536].End(3).Row
irow = .[O65536].End(3).Row
irow = .[P65536].End(3).Row
irow = .[Q65536].End(3).Row
arr = .Cells(6, icolumn).Resize(irow - 1)
End With
For i = 1 To UBound(arr)
If arr(i, 1) <> "" Then d(arr(i, 1)) = ""
Next
With icell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(d.keys, ",")
End With
End Sub
Sub 定义序列()
Dim arr, i%, ss$, brr
With Sheets("总人数")
arr = Application.Transpose(.[D5:Q5])
End With
For i = 1 To UBound(arr)
ss = ss & arr(i, 1) & ","
Next i
brr = Array(Range("E4"), Range("G10"), Range("I7"))
For i = 0 To 2
With brr(i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=ss
End With
Next i
End Sub
老师,我还是没有看懂这句中 irow = .[D65536].End(3).Row 里面End(3). 3表示什么
|
|