楼主
水星钓鱼 |
分享一个我每个月都在用的考勤表自动生成代码。只需修改- arr2 = Array("张三", "李四", "刘备")
此代码即可改为想要的结果。
- Private Sub CommandButton1_Click()
- Dim i, j, k, l, n, m, arr1, arr2
- Dim oWK As Worksheet
- j = Month(Now())
- arr1 = Array("姓名", "具体时间", "异常类别", "备注原因", "类别", "统计", "出勤天数")
- arr2 = Array("张三", "李四", "刘备")
- Set oWK = ThisWorkbook.Worksheets.Add
- With oWK
- .Name = Year(Now()) & "年" & j & "月考勤登记表"
- .Range("A1:G1") = arr1
- For i = 0 To UBound(arr2)
- For l = 1 To Day(DateSerial(Year(Now()), j + 1, 0))
- .Cells(k + 2, 1) = arr2(i)
- .Cells(k + 2, 2) = Format(DateSerial(Year(Now()), j, l), "yyyy-m-d")
- .Cells(k + 2, 3).FormulaR1C1 = "=IF(RC[2]=""零星假"",""■零星假 □迟到/旷工" & Chr(10) & "□公出 □漏打卡"",IF(RC[2]=""漏打卡"",""□零星假 □迟到/旷工" & Chr(10) & "□公出 ■漏打卡"",IF(COUNT(FIND(""出"",RC[2])),""□零星假 □迟到/旷工" & Chr(10) & "■公出 □漏打卡"","""")))"
- .Cells(k + 2, 6).FormulaR1C1 = "=IF(OR(AND(RC[-1]<>""零星假"",COUNT(FIND({""假"",""休""},RC[-1]))>0),AND(WEEKDAY(RC[-4],2)>5,COUNT(FIND({""班""},RC[-1]))=0)),""不在岗"",""在岗"")"
- k = k + 1
- Next
- Next
- n = .Range("a65536").End(xlUp).Row
- With .Range("e2:e" & n).Validation
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:="零星假,公出,漏打卡,加班,病假,事假,调休,集体异常,外出客服,借调,婚假,送托假,产假,陪产假,流产假,产检假,迟到/旷工,国家法定节假日,正常上班"
- End With
- .Cells.Locked = False
- .Cells.AutoFilter
- .Cells.EntireColumn.AutoFit
- .Range("a2").Select
- End With
- With ActiveWindow
- .FreezePanes = True
- End With
- End Sub
 考勤表自动生成.rar
|
