Sub Form_Run()
Dim i&, r&, ik&, eR&, eC&, sR&, sC&, sRow&
Dim aData(), aQLy(), Res(), s, Rng As Range
Dim Ngay As Date, dNam As Date, dThang As Date, cThang As Date, ThoiGian, GioPhut As String
Ngay = Date
With Sheets("Form").Range("A14:AD36")
ReDim Res(1 To .Rows.Count, 1 To .Columns.Count)
End With
Set Rng = Sheets("Form").Range("AG14:AG36")
With Sheets("Data")
eR = .Range("A" & Rows.Count).End(xlUp).Row
If eR < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
aData = .Range("A2:F" & eR).Value
sRow = UBound(aData) ' so dong Data
GioPhut = .Range("C1")
End With
With Sheets("Q-Ly")
sR = .Range("A1").End(xlDown).Row
sC = .Range("A1").End(xlToRight).Column
aQLy = .Range("A1").Resize(sR, sC).Value
End With
If Month(Ngay) = 12 And Day(Ngay) >= 16 Then'Dau Nam
dNam = DateSerial(Year(Ngay), 12, 16)
Else
dNam = DateSerial(Year(Ngay) - 1, 12, 16)
End If
If Day(Ngay) < 16 Then
dThang = DateSerial(Year(Ngay), Month(Ngay) - 1, 16)'Dau Thang
cThang = DateSerial(Year(Ngay), Month(Ngay), 15)'Cuoi thang
Else
dThang = DateSerial(Year(Ngay), Month(Ngay), 16)
cThang = DateSerial(Year(Ngay), Month(Ngay) + 1, 15)
End If
For j = 3 To sC 'dòng "Ngay" Sheets("Q-Ly")
If aQLy(1, j) = Ngay Then
For i = 2 To sR
ThoiGian = aQLy(i, j)
If ThoiGian > 0 Then
k = k + 1
Res(k, 1) = k
Res(k, 2) = aQLy(i, 1)
Res(k, 7) = aQLy(i, 2)
Res(k, 20) = ThoiGian
If Rng(k, 1) = "HC" Then s = 2
If Rng(k, 1) = "1" Then s = 3
If Rng(k, 1) = "2" Then s = 4
If Rng(k, 1) = "3" Then s = 5
'If Rng(k, 1) = "3" Then s = 6
For r = 1 To sRow
If aData(r, 1) = ThoiGian Then
Res(k, 15) = aData(r, s)
Exit For
End If
Next r
For jk = 3 To sC
If aQLy(1, j) > cThang Then Exit For
If aQLy(1, jk) >= dThang Then Res(k, 23) = Res(k, 23) + aQLy(i, jk)
If aQLy(1, jk) >= dNam Then Res(k, 24) = Res(k, 24) + aQLy(i, jk)
Next jk
Res(k, 25) = GioPhut
s = Split(Application.Trim(Res(k, 7)), " ")
Res(k, 29) = s(UBound(s))
End If
Next i
Exit For
End If
Next j
Sheets("Form").Range("A14").Resize(UBound(Res), 29) = Res
End Sub