Sub XYZ()
Dim sArr(), aCol(), Res()
Dim fDate As Date, eDate As Date
Dim i&, k&, kHang$, colNo$
With Sheet1
aBan = .Range("A2", .Range("G" & Rows.Count).End(xlUp)).Value
End With
With Sheet2
aThu = .Range("A2", .Range("F" & Rows.Count).End(xlUp)).Value
End With
With Sheet3
i = .Range("A" & Rows.Count).End(xlUp).Row
If i > 8 Then .Range("A9:G" & i).Clear
fDate = .Range("C4").Value2
eDate = .Range("C5").Value2
kHang = .Range("C6").Value2
End With
ReDim Res(1 To UBound(aBan) + UBound(aThu), 1 To 7)
aCol = Array(0, 1, 2, 3, 4, 7)
Call AddRes(aBan, "XK", aCol, Res, k, fDate, eDate, kHang)
aCol = Array(0, 1, 3, 4, 5, 6)
Call AddRes(aThu, "PC", aCol, Res, k, fDate, eDate, kHang)
If k > 0 Then
With Sheet3
.Range("A9").Resize(k, 7) = Res
.Range("A9").Resize(k, 7).Sort .[A9], 1, Header:=xlNo
Res = .Range("E9").Resize(k + 1, 3)
For i = 1 To k
Res(i, 3) = Res(i, 1) - Res(i, 2)
If i > 1 Then Res(i, 3) = Res(i, 3) + Res(i - 1, 3)
Res(k + 1, 1) = Res(k + 1, 1) + Res(i, 1)
Res(k + 1, 2) = Res(k + 1, 2) + Res(i, 2)
Next i
.Range("E9").Resize(k + 1, 3) = Res
.Range("A" & k + 9) = "Tong"
.Range("A" & k + 9).Resize(, 4).MergeCells = True
.Range("A9").Resize(k + 1, 7).Borders.LineStyle = 1
End With
End If
End Sub
Private Sub AddRes(ByVal sArr, ByVal strNo, aCol, Res, k, fDate, eDate, kHang)
Dim sRow&, i&, jk&, Phieu$
sRow = UBound(sArr)
For i = 1 To sRow
If sArr(i, 1) > eDate Then Exit For
If sArr(i, 1) >= fDate And sArr(i, aCol(3)) = kHang Then
If Phieu <> sArr(i, aCol(2)) Then
Phieu = sArr(i, aCol(2))
k = k + 1
For j = 1 To 4
Res(k, j) = sArr(i, aCol(j))
Next j
If Left(Phieu, 2) = strNo Then jk = 5 Else jk = 6
Res(k, jk) = sArr(i, aCol(5))
Else
Res(k, jk) = Res(k, jk) + sArr(i, aCol(5))
End If
End If
Next i
End Sub
