Option Explicit
Sub RaCong()
Dim Sh As Worksheet, Rng As Range, sRng As Range, Cls As Range
Dim MyAdd As String, sTG As String, sLoi As String
Dim Thg As Byte, Tre As Double, TrGio As Double, Dat As Date
Dim Gio As Integer, Phut As Integer, Gy As Integer, Nam As Integer
Set Sh = Sheets("MayGhi"): Sheets("Cong").Select
Thg = [a1].Value: Nam = Year([A2].Value)
Set Rng = Sh.Range(Sh.[a1], Sh.[a65500].End(xlUp))
On Error GoTo LoiCT: sLoi = "@"
[d6].Resize(200, 33).ClearContents
For Each Cls In Range([A6], [a65500].End(xlUp))
If Cls.Value <> "" Then
Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
If Not sRng Is Nothing Then
MyAdd = sRng.Address
Tre = 0: TrGio = 0
Do
Dat = sRng.Offset(, 2).Value
If Month(Dat) = Thg And Year(Dat) = Nam Then
If sRng.Offset(, 5).Value <> "" And sRng.Offset(, 6).Value > 0 Then
1 Cells(Cls.Row, 3 + Day(Dat)).Value = "X"
If sRng.Offset(, 7) <> "" Then
If IsNumeric(sRng.Offset(, 7)) Then MsgBox "IsNumeric", , "7"
Tre = Tre + QuyDoi(sRng.Offset(, 7).Value)
sRng.Offset(, 7).Interior.ColorIndex = 30 + Cls.Row
End If
If sRng.Offset(, 8) <> "" Then
If IsNumeric(sRng.Offset(, 7)) Then MsgBox "IsNumeric", , "8"
Tre = Tre + QuyDoi(sRng.Offset(, 8).Value)
sRng.Offset(, 8).Interior.ColorIndex = 31 + Cls.Row
End If
If sRng.Offset(, 9) <> "" Then
TrGio = TrGio + QuyDoi(sRng.Offset(, 9).Value)
End If
End If
End If
Set sRng = Rng.FindNext(sRng)
Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
If Tre > 0 Then Cells(Cls.Row, "AI").Value = Tre
If TrGio > 0 Then Cells(Cls.Row, "AJ").Value = TrGio
Tre = 0: TrGio = 0
Else: MsgBox "Nothing"
End If
End If
Next Cls
Err_: Exit Sub
LoiCT:
If Err = 13 Then
If InStr(sLoi, sRng.Address) < 1 Then sLoi = sLoi & sRng.Address
Resume Next
Else
Resume Err_
End If
End Sub
Function QuyDoi(sTG As String) As Double
Dim Gio As Integer, Phut As Integer, Gy As Integer
Gy = CInt(Right(sTG, 2))
If Len(sTG) > 6 Then
Gio = CInt(Left(sTG, 2)): Phut = CInt(Mid(sTG, 4, 2))
ElseIf Len(sTG) > 3 Then
Phut = CInt(Left(sTG, 2))
End If
QuyDoi = TimeSerial(Gio, Phut, Gy)
End Function
