vba考勤处理
sheet1 格式
Sub 统计人数() 'A为号码C姓名 D时间 Dim j As Integer j = 1 For i = 2 To 100000 Step 1 If Range("a" & i).Value <> Range("a" & i + 1).Value Then j = j + 1 Sheets("sheet3").Range("a" & j).Value = Range("a" & i).Value Sheets("sheet3").Range("b" & j).Value = Range("c" & i).Value Else: If Range("a" & i).Value = "" Then End End If Next i End Sub Sub 统计迟到早退() 'Sheets("sheet3").Range("c" & j).Value = 1 Dim a, b, c, j As Integer j = 1 For i = 2 To 100000 Step 1 a = Sheets("sheet1").Range("a" & i).Value b = Sheets("sheet3").Range("a" & j).Value c = Sheets("sheet1").Range("d" & i).Value If a = "" Then Exit For If a - b = 0 Then Sheets("sheet3").Range("c" & j).Value = Sheets("sheet3").Range("c" & j).Value + 0.5 '判断时间 经理9:00 员工9:20 d = Val(Format(c, "hhnn")) e = Format(c, "d") 'If d - 900 > 0 And d - 900 < 300 Then ' Sheets("sheet3").Range("i" & j).Value = Sheets("sheet3").Range("i" & j).Value & e & "、" ' End If If d - 920 > 0 And d - 920 < 280 Then Sheets("sheet3").Range("i" & j).Value = Sheets("sheet3").Range("i" & j).Value & e & "迟到、" End If If d - 1200 < 600 And d - 1200 > 0 Then Sheets("sheet3").Range("h" & j).Value = Sheets("sheet3").Range("h" & j).Value & e & "早退、" End If Else j = j + 1 Sheets("sheet3").Range("c" & j).Value = 0.5 'MsgBox (a) 'MsgBox (b) End If Next i End Sub Sub 统计上下午未打卡() Dim a, b, c, j As Integer j = 1 For i = 2 To 100000 Step 1 a = Sheets("sheet1").Range("a" & i).Value l = Sheets("sheet1").Range("a" & i + 1).Value b = Sheets("sheet3").Range("a" & j).Value c = Sheets("sheet1").Range("d" & i).Value d = Sheets("sheet1").Range("d" & i + 1).Value g = Sheets("sheet1").Range("d" & i - 1).Value e = Format(c, "d") f = Format(d, "d") h = Format(g, "d") k = Format(c, "h") If a = "" Then Exit For If a - b = 0 Then If e <> f And e <> h And k < 12 Then Sheets("sheet3").Range("j" & j).Value = Sheets("sheet3").Range("j" & j).Value & e & "下午、" End If If e <> f And e <> h And k > 12 Then Sheets("sheet3").Range("j" & j).Value = Sheets("sheet3").Range("j" & j).Value & e & "上午、" End If Else: j = j + 1 If e <> f And k > 12 Then Sheets("sheet3").Range("j" & j).Value = e & "上午\、" End If If k < 12 And e <> f Then Sheets("sheet3").Range("j" & j).Value = e & "下午\、" End If End If Next i End Sub Sub 填写第一行() Sheets("sheet3").Range("a" & 1).Value = "考勤号码" Sheets("sheet3").Range("b" & 1).Value = "姓名" Sheets("sheet3").Range("c" & 1).Value = "出勤天数" Sheets("sheet3").Range("h" & 1).Value = "早退" Sheets("sheet3").Range("i" & 1).Value = "迟到" Sheets("sheet3").Range("j" & 1).Value = "未打卡" End Sub Sub test() 'Dim arry(1 To 2) As Integer c = Sheets("sheet1").Range("d" & 29).Value 'arry(1) = 23 'arry(2) = 909 a = DateAdd("n", 20, c) MsgBox (Format(c, "hh") > 12) End Sub