|
楼主 |
发表于 2018-3-3 14:41
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Private Sub commandbuttonl_click()
Dim col%, gjz$
Dim arr, i&, aa, j&
Dim d, k, t
col = Val(Me.TextBox1.Text)
gjz = Me.TextBox2.Text
Set d = CreateObject("Scripting.Dictionary")
arr = [a1].CurrentRegion
If col > UBound(arr, 2) Or col < 0 Then MsgBox "列超出范围!": Exit Sub
If gjz = "" Then MsgBox "关键字不能为空!": Exit Sub
For i = 1 To UBound(arr)
If arr(i, col) = gjz Then d(gjz) = d(gjz) & i & ","
End If
Next
If d.Count > 0 Then
Sheet.Add after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = gjz
k = d.keys: t = d.items
For i = 0 To UBound(k)
t(i) = Left(t(i), Len(t(i)) - 1)
If InStr(t(i), ",") Then
aa = Split(t(i), ",")
For j = 0 To UBound(aa)
.Cells(j + 2, 1).Resize(1, UBound(arr, 2)) = Application.Index(arr, aa(j), 0)
Next
Else
.Cells(2, 1).Resize(1, UBound(arr, 2)) = Application.Index(arr, t(i), 0)
End If
Next
End With
End If
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub
|
|