Nhờ các anh/chị chỉ giúp em, em đang làm sheet "tổng hợp chi tiết công nợ" từ 2 sheet "phát sinh" và "thanh toán". Tham chiếu và lọc dữ liệu qua mã khách hàng. Chỉ giúp em code VBA để khi output không thể hiện dòng trống được không ạ. Em cảm ơn nhiều ạ
Sub loccongno()
'Buoc 1: Loc du lieu sheet phat sinh
Dim arr(), kq(), a As Long, i As Long, ir As Long
Dim shnguon As Worksheet, shdich As Worksheet
Set shnguon = Sheets("phat sinh")
Set shdich = Sheets("chi tiet cong no")
With shdich
makh = shdich.Range("C10").Value
End With
With shnguon
ir = .Range("A" & Rows.Count).End(xlUp).Row
arr = .Range("A5:V" & ir).Value
ReDim kq(1 To 10000, 1 To 8)
End With
For i = 1 To UBound(arr, 1)
If arr(i, 3) = makh Then
a = a + 1
kq(a, 1) = arr(i, 1) 'ngay thang
kq(a, 2) = arr(i, 2) 'so hoa don
kq(a, 3) = arr(i, 5) 'noi dung
kq(a, 4) = arr(i, 11) 'so tien phai thanh toan
kq(a, 5) = ""
kq(a, 6) = arr(i, 21) 'phan loai chi phi
kq(a, 7) = arr(i, 20) 'phap nhan
kq(a, 8) = arr(i, 22) 'ghi chu
End If
If arr(i, 3) <> makh Then
a = a + 1
kq(a, 1) = ""
kq(a, 2) = ""
kq(a, 3) = ""
kq(a, 4) = ""
kq(a, 5) = ""
kq(a, 6) = ""
kq(a, 7) = ""
kq(a, 8) = ""
End If
Next i
'Buoc 2: Loc du lieu sheet thanh toan
Set shnguon = Sheets("thanh toan")
With shnguon
ir = .Range("A" & Rows.Count).End(xlUp).Row
arr = .Range("A5:I" & ir).Value
End With
For i = 1 To UBound(arr, 1)
If arr(i, 2) = makh Then
a = a + 1
kq(a, 1) = arr(i, 1) 'ngay thang
kq(a, 2) = arr(i, 5) 'so hoa don
kq(a, 3) = arr(i, 4) 'noi dung
kq(a, 4) = ""
kq(a, 5) = arr(i, 6) 'so tien thanh toan
kq(a, 6) = ""
kq(a, 7) = arr(i, 8) 'phap nhan
kq(a, 8) = arr(i, 9) 'ghi chu
End If
If arr(i, 2) <> makh Then
a = a + 1
kq(a, 1) = ""
kq(a, 2) = ""
kq(a, 3) = ""
kq(a, 4) = ""
kq(a, 5) = ""
kq(a, 6) = ""
kq(a, 7) = ""
kq(a, 8) = ""
End If
Next i
'Output dan ket qua ra sheet chi tiet cong no
If a > 10000 Then
MsgBox "du lieu vuot qua 10000 dong!", vbCritical
Exit Sub
End If
If a > 0 Then
With shdich
.Range("A17:A10016").ClearContents
.Range("A17").Resize(a, 8).Value = kq
End With
End If
End Sub
Sub loccongno()
'Buoc 1: Loc du lieu sheet phat sinh
Dim arr(), kq(), a As Long, i As Long, ir As Long
Dim shnguon As Worksheet, shdich As Worksheet
Set shnguon = Sheets("phat sinh")
Set shdich = Sheets("chi tiet cong no")
With shdich
makh = shdich.Range("C10").Value
End With
With shnguon
ir = .Range("A" & Rows.Count).End(xlUp).Row
arr = .Range("A5:V" & ir).Value
ReDim kq(1 To 10000, 1 To 8)
End With
For i = 1 To UBound(arr, 1)
If arr(i, 3) = makh Then
a = a + 1
kq(a, 1) = arr(i, 1) 'ngay thang
kq(a, 2) = arr(i, 2) 'so hoa don
kq(a, 3) = arr(i, 5) 'noi dung
kq(a, 4) = arr(i, 11) 'so tien phai thanh toan
kq(a, 5) = ""
kq(a, 6) = arr(i, 21) 'phan loai chi phi
kq(a, 7) = arr(i, 20) 'phap nhan
kq(a, 8) = arr(i, 22) 'ghi chu
End If
If arr(i, 3) <> makh Then
a = a + 1
kq(a, 1) = ""
kq(a, 2) = ""
kq(a, 3) = ""
kq(a, 4) = ""
kq(a, 5) = ""
kq(a, 6) = ""
kq(a, 7) = ""
kq(a, 8) = ""
End If
Next i
'Buoc 2: Loc du lieu sheet thanh toan
Set shnguon = Sheets("thanh toan")
With shnguon
ir = .Range("A" & Rows.Count).End(xlUp).Row
arr = .Range("A5:I" & ir).Value
End With
For i = 1 To UBound(arr, 1)
If arr(i, 2) = makh Then
a = a + 1
kq(a, 1) = arr(i, 1) 'ngay thang
kq(a, 2) = arr(i, 5) 'so hoa don
kq(a, 3) = arr(i, 4) 'noi dung
kq(a, 4) = ""
kq(a, 5) = arr(i, 6) 'so tien thanh toan
kq(a, 6) = ""
kq(a, 7) = arr(i, 8) 'phap nhan
kq(a, 8) = arr(i, 9) 'ghi chu
End If
If arr(i, 2) <> makh Then
a = a + 1
kq(a, 1) = ""
kq(a, 2) = ""
kq(a, 3) = ""
kq(a, 4) = ""
kq(a, 5) = ""
kq(a, 6) = ""
kq(a, 7) = ""
kq(a, 8) = ""
End If
Next i
'Output dan ket qua ra sheet chi tiet cong no
If a > 10000 Then
MsgBox "du lieu vuot qua 10000 dong!", vbCritical
Exit Sub
End If
If a > 0 Then
With shdich
.Range("A17:A10016").ClearContents
.Range("A17").Resize(a, 8).Value = kq
End With
End If
End Sub