|
发表于 2013-7-3 16:17
|
显示全部楼层
本楼为最佳答案
股网千王 发表于 2013-7-3 13:18
老师您好,我测试了你的代码,若直接点击B列就会出现1004的错误”不能取得worksheetFounction的match属性 ... - Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dim ir%, x%, st$
- On Error GoTo ok
- If Target.Column = 2 Then
- ir = Application.WorksheetFunction.Match(Target.Offset(0, -1), Sheets("资料").Range("a:a"), 0)
- Do
- x = x + 1
- Loop Until (Sheets("资料").Range("a" & ir + x) = "" And Sheets("资料").Range("b" & ir + x) = "") Or Sheets("资料").Range("a" & ir + x) <> ""
- st = Sheets("资料").Range("b" & ir).Resize(x, 1).Address
- ActiveWorkbook.Names.Add Name:="二级", RefersTo:="=资料!" & st
- With Selection.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:="=二级"
- End With
- End If
- ok:
- End Sub
复制代码 |
|