Sub BBDCCN()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long, K As Long, Dcuoi As Long, Tensheet$, Sht$
Dim ArrN(), ArrD()
'On Error Resume Next
Tensheet = Sheets("DCCN").Range("C2").Value
If Tensheet = "" Then MsgBox "Khong duoc de trong": Exit Sub
If Tensheet = "PN" Then
Sht = "NHAP"
Else
Sht = "XUAT"
End If
Dcuoi = Sheets(Sht).Range("B1000000").End(xlUp).Row
ArrN = Sheets(Sht).Range("B3:N" & Dcuoi).Value
ReDim ArrD(1 To UBound(ArrN, 1), 1 To 10)
tungay = Sheets("DCCN").[E2].Value
denngay = Sheets("DCCN").[G2].Value
KH = Sheets("DCCN").[J2].Value
If tungay > denngay Then
MsgBox "Tu ngay den ngay khong dung !"
Sheets("DCCN").[E2].Select
Exit Sub
End If
For i = 1 To UBound(ArrN, 1)
If ArrN(i, 3) <> "" Then
If tungay <= ArrN(i, 3) And ArrN(i, 3) <= denngay And ArrN(i, 4) = KH Then
K = K + 1
ArrD(K, 1) = K
ArrD(K, 2) = ArrN(i, 2)
ArrD(K, 3) = ArrN(i, 3)
ArrD(K, 4) = ArrN(i, 8)
ArrD(K, 5) = ArrN(i, 10)
ArrD(K, 6) = ArrN(i, 11)
ArrD(K, 7) = ArrN(i, 9)
ArrD(K, 8) = ArrN(i, 12)
ArrD(K, 9) = ArrN(i, 13)
ArrD(K, 10) = ArrN(i, 1)
End If
End If
Next
If K = 0 Then
MsgBox "Chua co phat sinh"
Exit Sub
Else
With Sheets("DCCN")
.Range("B17:K" & Dcuoi).Clear
.Range("G17").Offset(K, 0) = "C" & ChrW(7897) & "ng ti" & ChrW(7873) & "n hàng"
.Range("G17").Offset(K + 1, 0) = "Ti" & ChrW(7873) & "n thu" & ChrW(7871) & " GTGT 10%"
.Range("G17").Offset(K + 2, 0) = "T" & ChrW(7893) & "ng c" & ChrW(7897) & "ng ti" & ChrW(7873) & "n thanh toán"
.Range("J17").Offset(K, 0) = "=SUM(R17C:R[-1]C)"
.Range("J17").Offset(K + 1, 0) = "=ROUND(R[-1]C*10%,0)"
.Range("J17").Offset(K + 1, 0).Font.Underline = xlUnderlineStyleSingle
.Range("J17").Offset(K + 2, 0) = "=R[-2]C+R[-1]C"
.Range("B17").Resize(K + 3, 1).HorizontalAlignment = xlCenter
.Range("C17").Resize(K + 3, 1).HorizontalAlignment = xlCenter
.Range("C17").Resize(K + 3, 1).NumberFormat = "0000"
.Range("D17").Resize(K + 3, 1).HorizontalAlignment = xlCenter
.Range("D17").Resize(K + 3, 1).NumberFormat = "dd/mm/yyyy"
.Range("F17").Resize(K + 3, 1).NumberFormat = "#,##0.0"
.Range("G17").Resize(K + 3, 1).NumberFormat = "#,##0.0000"
.Range("H17").Resize(K + 3, 1).HorizontalAlignment = xlCenter
.Range("I17").Resize(K + 3, 2).NumberFormat = "#,##0"
.Range("I17").Resize(K + 3, 2).NumberFormat = "#,##0"
.Range("I17").Resize(K + 3, 2).NumberFormat = "#,##0;[Red](#,##0)"
.Range("J17").Resize(K + 3, 2).NumberFormat = "#,##0;[Red](#,##0)"
.Range("K17").Resize(K + 3, 1).HorizontalAlignment = xlCenter
.Range("B17").Resize(K + 0, 9).Borders.ColorIndex = 16
.Range("B17").Resize(K + 0, 9).Borders.LineStyle = 1
.Range("B17").Offset(K, 0).Resize(3, 9).Font.Bold = True
.Range("B17").Resize(K, 10) = ArrD
.Range("D17").Offset(K + 3, 0) = "Thành ti" & ChrW(7873) & "n b" & ChrW(7857) & "ng ch" & ChrW(7919) & ":"
.Range("D17").Offset(K + 3, 0).HorizontalAlignment = xlRight
.Range("B17").Offset(K + 3, 0).Resize(1, 9).Font.Italic = True
.Range("E17").Offset(K + 3, 0).Value = "=tvnd(R[-1]C[5])"
.Range("D17").Offset(K + 5, 0) = "KH" & ChrW(193) & "CH H" & ChrW(192) & "NG"
.Range("H17").Offset(K + 5, 0) = "NG" & ChrW(431) & ChrW(7900) & "I L" & ChrW(7852) & "P BI" & ChrW(7874) & "U"
.Range("D17").Offset(K + 5, 0).Resize(3, 9).Font.Bold = True
.Range("B17:K" & Dcuoi).VerticalAlignment = xlCenter
.Range("B17:K" & Dcuoi).Font.Name = "Times New Roman"
.Range("B17:K" & Dcuoi).Font.Size = 13
.Range("B17:K" & Dcuoi).RowHeight = 30
.Range("E17").Resize(K + 3, 1).InsertIndent 1
.Range("B17").Offset(K + 4, 0).RowHeight = 8
End With
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub