|
发表于 2012-3-18 15:45
|
显示全部楼层
本楼为最佳答案
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim ar, st, br(), cr()
- Dim i As Integer, j As Integer, k As Integer
- If Target.Address <> "$B$2" Then Exit Sub
- Range("c2:c65536").ClearContents
- Range("d2:d65536").ClearContents
- st = Range("b2")
- ar = Range("a2:a" & Range("a65536").End(xlUp).Row)
- ReDim br(1 To UBound(ar))
- ReDim cr(1 To UBound(ar))
- For i = 1 To UBound(ar)
- If ar(i, 1) Like "*" & st Then
- j = j + 1
- br(j) = ar(i, 1)
- ElseIf ar(i, 1) Like "*" & st & "*" And Not ar(i, 1) Like "*" & st Then
- k = k + 1
- cr(k) = ar(i, 1)
- End If
- Next
- If j > 0 Then Range("c2").Resize(j, 1) = Application.Transpose(br)
- If k > 0 Then Range("d2").Resize(k, 1) = Application.Transpose(cr)
- End Sub
复制代码 放在工作表事件里
自动检索填充.rar
(15.69 KB, 下载次数: 9)
|
|