Sub chuyen()
Dim arrkq(), dl, i, j
dl = Sheets("canchuyen").Range(Sheets("canchuyen").[a1], Sheets("canchuyen").[a65536].End(3)).Value
ReDim arrkq(1 To UBound(dl), 1 To 10)
For i = 1 To UBound(dl)
If dl(i, 1) = "" Then
j = j + 1
arrkq(j, 1) = Left(dl(i + 1, 1), InStrRev(dl(i + 1, 1), ",") - 1)
arrkq(j, 1) = Trim(Right(arrkq(j, 1), Len(arrkq(j, 1)) - InStrRev(arrkq(j, 1), ",") - 1))
arrkq(j, 2) = dl(i - 1, 1)
arrkq(j, 3) = Trim(Right(dl(i + 4, 1), Len(dl(i + 4, 1)) - 1 - InStrRev(dl(i + 4, 1), ":")))
arrkq(j, 4) = Trim(Mid(dl(i + 3, 1), 1 + InStr(dl(i + 3, 1), ":"), InStr(dl(i + 3, 1), "| ") - 1 - InStr(dl(i + 3, 1), ":")))
arrkq(j, 4) = Application.Substitute(arrkq(j, 4), ChrW(160), "")
arrkq(j, 5) = Trim(Right(dl(i + 1, 1), Len(dl(i + 1, 1)) - 1 - InStr(dl(i + 1, 1), ":")))
arrkq(j, 6) = Trim(Mid(dl(i + 2, 1), 1 + InStr(dl(i + 2, 1), ":"), InStr(dl(i + 2, 1), "|") - 1 - InStr(dl(i + 2, 1), ":")))
arrkq(j, 6) = Application.Substitute(arrkq(j, 6), ChrW(160), "")
arrkq(j, 7) = Trim(Right(dl(i + 2, 1), Len(dl(i + 2, 1)) - 1 - InStrRev(dl(i + 2, 1), ":")))
arrkq(j, 8) = Trim(Right(dl(i + 6, 1), Len(dl(i + 6, 1)) - 10))
arrkq(j, 9) = Format(Right(dl(i + 3, 1), 10), "dd-mmm-yyyy")
arrkq(j, 10) = Format(Right(dl(i + 5, 1), 10), "dd-mmm-yyyy")
End If
Next
With Sheet2
.[b2:k10000].ClearContents
.[b2].Resize(j, 10) = arrkq
.Range(.[b2], .[b65536].End(3)).Offset(, -1) = [row(a:a)]
End With
End Sub
