Sub TachDulieu()
Dim r As Long, lastRow As Long, so_chisole As Long, pos As Long, data(), result()
Const ngoac As String = "]"
With ThisWorkbook.Worksheets("Sheet1")
' luon luon phai xoa ket qua cu
lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
Union(.Range("B1").Resize(lastRow), .Range("D1").Resize(lastRow), .Range("F1").Resize(lastRow)).ClearContents
' lay cot G vao mang. Mang data cung duoc dung lam mang ket qua cho cot B
data = .Range("G1:G" & .Cells(Rows.Count, "G").End(xlUp).Row + 1).Value
End With
' mang ket qua cho cot F
ReDim result(1 To UBound(data), 1 To 1)
For r = 1 To UBound(data) - 1 ' mang data da duoc lay du 1 dong
pos = InStr(1, data(r, 1), ngoac) ' tim ky tu ]
result(r, 1) = Mid(data(r, 1), pos + 1) ' ket qua cho cot F
If pos > 2 Then data(r, 1) = Mid(data(r, 1), 2, pos - 2) ' code phuc vu ca du lieu rong nen phai kiem tra pos > 2
Next r
ThisWorkbook.Worksheets("Sheet1").Range("B1").Resize(UBound(data) - 1).Value = data ' nhap ket qua vao cot B
ThisWorkbook.Worksheets("Sheet1").Range("F1").Resize(UBound(data) - 1).Value = result ' nhap ket qua vao cot F
' neu du lieu cot G co it nhat 3 dong thi moi thuc hien - cot it nhat la B3
' trong data hien co ket qua cua cot B. Ta dung mang data lam mang ket qua cho cot D
If UBound(data) > 3 Then
' so cac chi so le trong mang data - 3, 5, 7, ... ung voi B3, B5, B7, ...
so_chisole = (UBound(data) - 4) \ 2 + 1 ' nen nho la mang data duoc lay du 1 dong
For r = 1 To so_chisole
data(2 * r - 1, 1) = data(2 * r + 1, 1)
data(2 * r, 1) = data(2 * r + 1, 1)
Next r
ThisWorkbook.Worksheets("Sheet1").Range("D1").Resize(2 * so_chisole).Value = data ' nhap ket qua vao cot D
End If
ThisWorkbook.Worksheets("Sheet1").Range("G1").Resize(UBound(data) - 1).ClearContents ' xoa cot G
End Sub