|
发表于 2016-4-5 13:16
|
显示全部楼层
本楼为最佳答案
本帖最后由 yorkchenshunan 于 2016-4-5 13:18 编辑
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address = "$A$2" Then
- Range("a6:c65535").ClearContents
- Dim ar, br, n, m
- If Application.WorksheetFunction.CountIf(Sheets("明细").Range("a:a"), Target) Then
- ar = Sheets("明细").Range("a1").CurrentRegion
- ReDim br(1 To UBound(ar), 1 To 3)
- For n = 1 To UBound(ar)
- If ar(n, 1) = Target Then
- m = m + 1
- br(m, 1) = ar(n, 2)
- br(m, 2) = ar(n, 3)
- br(m, 3) = ar(n, 4)
- End If
- Next n
- Range("a6").Resize(m, 3) = br
- Else
- MsgBox "查无此人"
- End If
- End If
- End Sub
复制代码 |
|