|
|
楼主 |
发表于 2018-8-23 09:15
|
显示全部楼层
|
Private Sub CommandButton1_Click() Unload Me End Sub Private Sub Image1_Click() End Sub Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader) On Error GoTo myerr ListView1.SortKey = ColumnHeader.Index - 1 If ListView1.SortOrder = lvwDescending Then ListView1.SortOrder = lvwAscending Else ListView1.SortOrder = lvwDescending End If ListView1.Sorted = True myerr: Exit Sub End Sub Private Sub TextBox1_Change() Dim what As String: what = TextBox1.Value Dim rw&: rw = Sheet1.Range("A65536").End(xlUp).Row 'Dim total 'total = 0 If TextBox1.Value = "" Then ' 如果文本框内容为空,导入所有数据 ListView1.ListItems.Clear With Sheet1 For i = 2 To rw Set ITM = ListView1.ListItems.Add() ITM.Text = .Cells(i, 1) ITM.SubItems(1) = .Cells(i, 2) ITM.SubItems(2) = .Cells(i, 3) ITM.SubItems(3) = .Cells(i, 4) ITM.SubItems(4) = .Cells(i, 5) ITM.SubItems(5) = .Cells(i, 6) ITM.SubItems(6) = .Cells(i, 7) ITM.SubItems(7) = .Cells(i, 8) ITM.SubItems(8) = .Cells(i, 9) ITM.SubItems(9) = .Cells(i, 10) ITM.SubItems(10) = .Cells(i, 11) ITM.SubItems(11) = .Cells(i, 12) ITM.SubItems(12) = .Cells(i, 13) ITM.SubItems(13) = Format(.Cells(i, 14), "#,##0") 'total = total + 1 '.Cells(i, 13).Value Next i End With Label2.Caption = "共找到 " & ListView1.ListItems.Count & " 条记录" Label3.Caption = "总计: " & Format(rw - 1, "#,##0") TextBox1.SetFocus Exit Sub Else Dim rng As Range Set rng = Sheet1.Range("A2:M" & rw) ' 关键字所在的区域 Dim dic As Object Set dic = CreateObject("scripting.dictionary") ListView1.ListItems.Clear ' 清除所有内容 For i = 2 To rw 'For ii = 1 To 15 Dim tmp As Range Set tmp = Sheet1.Rows(i).Find(what, lookat:=xlPart, MatchCase:=False) ' 部分匹配, 不区分大小写 If Not tmp Is Nothing Then If Not dic.exists(tmp.Row) Then ' 因为是一行多列查找,所以只要有一个单元格符合要求,要需要换行,否则listitem数据重复 dic.Add tmp.Row, "" Set ITM = ListView1.ListItems.Add() ITM.Text = Sheet1.Cells(i, 1) ITM.SubItems(1) = Sheet1.Cells(i, 2) ITM.SubItems(2) = Sheet1.Cells(i, 3) ITM.SubItems(3) = Sheet1.Cells(i, 4) ITM.SubItems(4) = Sheet1.Cells(i, 5) ITM.SubItems(5) = Sheet1.Cells(i, 6) ITM.SubItems(6) = Sheet1.Cells(i, 7) ITM.SubItems(7) = Sheet1.Cells(i, 8) ITM.SubItems(8) = Sheet1.Cells(i, 9) ITM.SubItems(9) = Sheet1.Cells(i, 10) ITM.SubItems(10) = Sheet1.Cells(i, 11) ITM.SubItems(11) = Sheet1.Cells(i, 12) ITM.SubItems(12) = Sheet1.Cells(i, 13) ITM.SubItems(13) = Format(Sheet1.Cells(i, 14), "#,##0") 'total = total + 1 'Sheet1.Cells(i, 13).Value End If End If 'Next ii Next i Label2.Caption = "共找到 " & ListView1.ListItems.Count & " 条记录" Label3.Caption = "总计: " & Format(rw - 1, "#,##0") Set dic = Nothing End If End Sub Private Sub UserForm_Initialize() ListView1.ColumnHeaders.Add , , "姓名" ListView1.ColumnHeaders.Add , , "办公电话", Width / 7, lvwColumnCenter ListView1.ColumnHeaders.Add , , "手机", Width / 8, lvwColumnCenter ListView1.ColumnHeaders.Add , , "电子邮件", Width / 5, lvwColumnCenter ListView1.ColumnHeaders.Add , , "住址电话", Width / 7, lvwColumnCenter ListView1.ColumnHeaders.Add , , "备用手机", Width / 8, lvwColumnCenter ListView1.ColumnHeaders.Add , , "岗位", Width / 6, lvwColumnCenter ListView1.ColumnHeaders.Add , , "室", Width / 6, lvwColumnCenter ListView1.ColumnHeaders.Add , , "部门", Width / 6, lvwColumnCenter ListView1.ColumnHeaders.Add , , "姓名PY", Width / 12, lvwColumnCenter ListView1.ColumnHeaders.Add , , "岗位PY", Width / 10, lvwColumnCenter ListView1.ColumnHeaders.Add , , "室PY", Width / 10, lvwColumnCenter ListView1.ColumnHeaders.Add , , "部门PY", Width / 10, lvwColumnCenter ListView1.ColumnHeaders.Add , , "部", Width / 10, lvwColumnCenter ListView1.View = lvwReport ' listivew的显示格式为报表格式 'ListView1.Sorted = True ' listivew的排序属性为true 'ListView1.SortKey = 0 ' listivew排序的索引为 '姓名', 从 0 开始 ListView1.Gridlines = True ' 显示网格线 ListView1.FullRowSelect = True ' 允许整行选中 Label2.Caption = "" Label3.Caption = "" '设置循环,填充记录 Dim rw& rw = Sheet1.Range("A65536").End(xlUp).Row 'Dim total 'total = 0 With Sheet1 For i = 2 To rw Set ITM = ListView1.ListItems.Add() ITM.Text = .Cells(i, 1) ITM.SubItems(1) = .Cells(i, 2) ITM.SubItems(2) = .Cells(i, 3) ITM.SubItems(3) = .Cells(i, 4) ITM.SubItems(4) = .Cells(i, 5) ITM.SubItems(5) = .Cells(i, 6) ITM.SubItems(6) = .Cells(i, 7) ITM.SubItems(7) = .Cells(i, 8) ITM.SubItems(8) = .Cells(i, 9) ITM.SubItems(9) = .Cells(i, 10) ITM.SubItems(10) = .Cells(i, 11) ITM.SubItems(11) = .Cells(i, 12) ITM.SubItems(12) = .Cells(i, 13) ITM.SubItems(13) = Format(.Cells(i, 14), "#,##0") 'total = total + 1 '.Cells(i, 13).Value Next i End With Label2.Caption = "共找到 " & ListView1.ListItems.Count & " 条记录" Label3.Caption = "总计: " & Format(rw - 1, "#,##0") TextBox1.SetFocus End Sub |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-8-23 09:14
