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