Public Sub Loc()
[COLOR=#0000cd]'Khai bao cac bien[/COLOR]
Dim SrcArr, ResArr()
Dim lR As Long, lC As Long, k As Long
Dim sTag As String
[COLOR=#0000cd]'Tat che do cap nhat man hinh[/COLOR]
Application.ScreenUpdating = False
[COLOR=#0000cd]'Lay du lieu sheet "tong hop" vao mang SrcArr[/COLOR]
SrcArr = Sheet12.Range(Sheet12.[A15], Sheet12.[A65000].End(xlUp)).Resize(, 10).Value
With Sheet1
[COLOR=#0000cd]'Dat sTag = du lieu o sheet1 o J3[/COLOR]
sTag = UCase(.[J3].Value)
[COLOR=#0000cd]'Khai bao lai mang ResArr[/COLOR]
ReDim ResArr(1 To UBound(SrcArr, 1), 1 To 9)
[COLOR=#0000cd] 'dung vong lap duyet qua tung dong cua mang[/COLOR]
For lR = 1 To UBound(SrcArr, 1)
[COLOR=#0000cd] ' Neu dong nao trong cot 9 thoa dieu kien thi...[/COLOR]
If UCase(SrcArr(lR, 9)) = sTag Then
[COLOR=#0000cd] 'Danh dau dong do va dong thoi tao dong trong mang ResArr[/COLOR]
k = k + 1
[COLOR=#0000cd] 'Dung vong lap duyet qua 8 cot lien ke cua mang SrcArr[/COLOR]
For lC = 1 To 8
[COLOR=#0000cd] 'Lay du lieu tung cot trong mang SrcArr khi thoa DK cho vao mang ResArr[/COLOR]
ResArr(k, lC) = SrcArr(lR, lC)
[COLOR=#0000cd]' Lay du lieu o cot 10 mang SrcArr cho vao cot 9 mang ResArr[/COLOR]
ResArr(k, 9) = SrcArr(lR, 10)
[COLOR=#0000cd] 'Duyet tiep den het 8 cot trong mang SrcArr[/COLOR]
Next lC
[COLOR=#0000cd] 'ket thuc dk[/COLOR]
End If
[COLOR=#0000cd]'Duyet tiep den het dong trong mang SrcArr[/COLOR]
Next lR
[COLOR=#0000cd]'Neu co du lieu thoa dk thi[/COLOR]
If k Then
With .[A7]
[COLOR=#0000cd] 'Xoa vung co du lieu cu[/COLOR]
.Resize(lR, 9).Clear
[COLOR=#0000cd] ' Gan du lieu tu mang ResArr xuong sheet[/COLOR]
.Resize(k, 9).Value = ResArr
'Ke o vung du lieu
.Resize(k + 1, 9).Borders.LineStyle = xlContinuous
[COLOR=#0000cd] 'Nhap "Total" vao cot A dong cuoi cung du lieu[/COLOR]
.Offset(k) = "Total:"
[COLOR=#0000cd] 'Format dam dong cuoi cung[/COLOR]
.Offset(k).Resize(, 9).Font.Bold = True
[COLOR=#0000cd]'Tinh tong "Thu, Chi Ton" (dong cuoi)[/COLOR]
.Offset(k, 5).Resize(, 3).FormulaR1C1 = "=SUM(R7C:R[-1]C)"
[COLOR=#0000cd]'Format So tien dang "#,##0"[/COLOR]
.Offset(, 5).Resize(k + 1, 3).NumberFormat = "#,##0"
[COLOR=#0000cd] 'Format 2 cot ngay dang "dd/mm/yyyy"[/COLOR]
.Offset(, 8).Resize(k).NumberFormat = "dd/mm/yyyy"
.Resize(k).NumberFormat = "dd/mm/yyyy"
End With
Else
[COLOR=#0000cd]'Khong thi thong bao khong co du lieu[/COLOR]
MsgBox "No record found"
[COLOR=#0000cd] 'Ket thuc dk[/COLOR]
End If
End With
[COLOR=#0000cd]'Bat che do nhat man hinh[/COLOR]
Application.ScreenUpdating = True
End Sub
