【代码1】数据查询「方法1」：字典+SortedList，查询最近时间手机号
文摘
教育
2024-10-03 12:36
江苏
- 「方法1」：字典+SortedList，查询最近时间手机号|完整代码
1、在工作表“Sheet1”里，命令按钮点击事件，调用myQuery查询过程：Private Sub CmdQuery_Click() Call myQueryEnd Sub
2、在myModule模块里，myQuery过程，查询数据：Sub myQuery() Dim ws As Worksheet, iRow As Integer, rng As Range Dim arr(), temp() Dim srtList As Object, dic As Object Dim currTime As String, phone, plate, lastTime As String Set ws = ThisWorkbook.Sheets("Sheet1") Set srtList = CreateObject("System.Collections.SortedList") Set dic = CreateObject("Scripting.Dictionary") With ws lastRow = .UsedRange.Rows.Count arr = .Range(.Cells(1, 1), .Cells(lastRow, 4)).Value End With For i = 2 To UBound(arr) plate = arr(i, 4) If plate <> "" Then currTime = Format(arr(i, 2), "YYYYMMDDhhmmss") phone = arr(i, 3) If Not srtList.contains(plate) Then '//把车牌号、手机号存入SortedList srtList.Add plate, phone '//把车牌号、最新时间存入字典 dic(plate) = currTime Else If srtList(plate) = "" Then '//如果已保存的手机号是空的，换存 srtList(plate) = phone Else If phone <> "" Then '//如果当前手机号不为空，比较时间 lastTime = dic(plate) If currTime > lastTime Then '//如果当前时间是最新时间，替换手机号 srtList(plate) = phone '//如果当前时间是最新时间，替换最新时间 dic(plate) = curtime End If End If End If End If End If Next
第二部分，把字典数据展开到数组temp，再写入目标工作表： iRow = srtList.Count ReDim temp(1 To srtList.Count, 1 To 2) For i = 1 To iRow plate = srtList.getkey(i - 1) temp(i, 1) = plate temp(i, 2) = srtList(plate) Next With ws .Range(.Cells(2, 9), .Cells(.UsedRange.Rows.Count, 10)).Clear Set rng = .Cells(2, 9).Resize(iRow, 2) With rng .Value = temp .BorderAround xlContinuous, xlThin, 3 End With End With MsgBox "查询完成!"End Sub
![]()
| 安利小店
安利的牙膏非常不错，用了以后就不想再用其他的了；洗洁精、洗衣液也是日常必备，用过都说好！ |
![]()
| 合谷医疗
合谷医疗专攻各种疑难杂症，尤其擅长抑郁症、焦虑失眠、儿童神经发育异常、多动症、自闭孤独症、腰颈椎疾病治疗，可谓神乎其技！体验过的直呼早点来就好了！ |
喜欢就点个赞、点在看、留言评论、分享一下呗！感谢支持！
