Sub Kaoqin()
Dim MxSht, MxsR As Long, MxNRng As Range, NameArr
Dim MaxR As Long, MaxC As Byte, KaoQ() As String
Dim NBoot As Boolean, StaR As Long, TemRng, Cou As Long
Dim iR As Long, iC As Byte, MxR As Long, MxC As Byte
Sheet1.Range("D5:AI104").UnMerge
With Sheet2
Rem
MxsR = .Cells(1, 1).End(xlDown).Row
' MxSht = .Cells(1, 1).Resize(MxsR, 6).Value
Set MxNRng = .Cells(1, 2).Resize(MxsR)
MaxR = Me.Cells(5, 3).End(xlDown).Row - 4
MaxC = Me.Cells(4, Columns.Count).End(xlToLeft).Column - 4
ReDim KaoQ(1 To MaxR, 1 To MaxC)
NameArr = Me.Cells(5, 2).Resize(MaxR).Value
For iR = 1 To MaxR Step 2
Rem 姓名存在否
NBoot = WorksheetFunction.CountIf(MxNRng, NameArr(iR, 1))
Rem 存在时处理考勤数据
If NBoot Then
StaR = WorksheetFunction.Match(NameArr(iR, 1), MxNRng, 0)
Cou = WorksheetFunction.CountIf(MxNRng, NameArr(iR, 1))
TemRng = .Cells(StaR, 2).Resize(Cou, 5).Value
Rem 日期核对数据写入,上班下班的数据
For MxR = 1 To Cou
KaoQ(iR, Day(TemRng(MxR, 3))) = TemRng(MxR, 4) '对应日期上班时间数据
KaoQ(iR + 1, Day(TemRng(MxR, 3))) = TemRng(MxR, 5) '对应日期下班时间数据
Next MxR
End If
Next iR
End With
Me.Cells(5.1).Resize(MaxR) = NameArr
Me.Cells(5, 4).Resize(MaxR, MaxC) = KaoQ
End Sub