vba学习系列(8)--指定列单元格时间按时间段计数

Sub AssignTimeSegments() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称 Dim lastRow As Long lastRow = ws.Cells(ws.Rows.Count, "N").End(xlUp).Row ' 获取N列最后一个非空单元格的行号 Dim i As Long For i = 2 To lastRow ' 假设数据从N2开始 Dim cellTime As Date cellTime = ws.Cells(i, "N").Value ' 读取N列的时间 Dim segment As Integer Select Case cellTime Case "9:00:00" To "10:00:00" segment = 1 Case "10:00:01" To "10:10:00" segment = 2 Case "10:10:01" To "10:20:00" segment = 3 Case "10:20:01" To "10:30:00" segment = 4 Case "10:30:01" To "10:40:00" segment = 5 Case "10:40:01" To "10:50:00" segment = 6 Case "10:50:01" To "11:00:00" segment = 7 Case "11:00:01" To "11:10:00" segment = 8 Case "11:10:01" To "11:20:00" segment = 9 Case "11:20:01" To "11:30:00" segment = 10 Case "11:30:01" To "11:40:00" segment = 11 Case "11:40:01" To "11:50:00" segment = 12 Case "11:50:01" To "12:00:00" segment = 13 Case "12:00:01" To "13:00:00" segment = 14 Case "16:00:00" To "16:30:00" segment = 15 Case "16:30:01" To "16:40:00" segment = 16 Case "16:40:01" To "16:50:00" segment = 17 Case "16:50:01" To "17:00:00" segment = 18 Case "17:00:01" To "17:10:00" segment = 19 Case "17:10:01" To "17:20:00" segment = 20 Case "17:20:01" To "17:30:00" segment = 21 Case "17:30:01" To "17:40:00" segment = 22 Case "17:40:01" To "17:50:00" segment = 23 Case "17:50:01" To "18:00:00" segment = 24 Case "18:00:01" To "18:30:00" segment = 25 Case "22:00:00" To "23:00:00" segment = 26 Case "23:00:01" To "23:10:00" segment = 27 Case "23:10:01" To "23:20:00" segment = 28 Case "23:20:01" To "23:30:00" segment = 29 Case "23:30:01" To "23:40:00" segment = 30 Case "23:40:01" To "23:50:00" segment = 31 Case "23:50:01" To "23:59:59" segment = 32 Case "0:00:00" To "1:00:00" segment = 33 End Select ws.Cells(i, "O").Value = segment ' 在O列输出对应的数字 Next i End Sub
上一篇:分治


下一篇:07 设计模式-结构型模式-桥接模式-3 Demo代码