Sub ABC()
Dim aDC, aThue, res(), fso As Object, wb As Object
Dim sRow&, i&, r&, j&, t
aDC = Array("", "E4", "G8", "H23", "D31", "J41", "J43", "P45", "L55", _
"", "X70", "AB70", "", "", "", "K87") 'X1: tinh tong cot 9,"H68", "H69", "H70"
aThue = Array("NK", "GT", "MT")
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Chon Cac File to khai"
.Filters.Add "Excel Files", "*.xls", 1
.AllowMultiSelect = True
If .Show Then
sRow = .SelectedItems.Count
ReDim res(1 To sRow, 1 To 15)
For i = 1 To sRow
Set wb = Workbooks.Open(.SelectedItems(i))
res(i, 1) = Mid(Range(aDC(1)).Value, 1, 11)
t = Range(aDC(2)).Value
res(i, 2) = DateValue(Mid(t, 7, 4) & Mid(t, 3, 4) & Mid(t, 1, 2))
For j = 3 To 15
If aDC(j) <> "" Then res(i, j) = Range(aDC(j)).Value
Next j
res(i, 5) = Mid(res(i, 5), 5)
res(i, 6) = DateValue(Mid(res(i, 6), 7, 4) & Mid(res(i, 6), 3, 4) & Mid(res(i, 6), 1, 2))
If res(i, 7) <> Empty Then res(i, 7) = CDbl(Replace(Replace(res(i, 7), ".", ""), ",", "."))
If res(i, 8) <> Empty Then res(i, 8) = CDbl(Replace(Replace(res(i, 8), ".", ""), ",", "."))
res(i, 9) = res(i, 7) + res(i, 8)
If res(i, 11) <> Empty Then res(i, 11) = CDbl(Replace(Replace(res(i, 11), ".", ""), ",", "."))
For r = 68 To 73
If Range("D" & r).Value = Empty Then Exit For
t = Right(Range("D" & r).Value, 2)
For j = 0 To 2
If aThue(j) = t Then
res(i, 12 + j) = CDbl(Replace(Replace(Range("H" & r).Value, ".", ""), ",", ".")) 'Range("H" & r).Value
Exit For
End If
Next j
Next r
wb.Close False
Next i
End If
End With
i = Range("A" & Rows.Count).End(xlUp).Row
If i > 10 Then Range("A11:O" & i).ClearContents
If sRow Then Range("A11").Resize(sRow, 15) = res
Application.ScreenUpdating = True
End Sub
