1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
Sub test()
Dim WB As Workbook, dSh As Worksheet, oSh As Worksheet
Dim MaxY, MinY, CurY, R, X
Workbooks.OpenText "H:\DATA\test.txt", xlMSDOS, , xlDelimited, , , True, , , , , , , , ".", ","
Set WB = ActiveWorkbook
Set oSh = WB.Sheets(1)
oSh.Cells.EntireColumn.AutoFit
Set dSh = WB.Sheets.Add(after:=WB.Sheets(1))
MaxY = Year(Application.WorksheetFunction.Max(oSh.Range("A:A")))
MinY = Year(Application.WorksheetFunction.Min(oSh.Range("A:A")))
R = 1
Do Until oSh.Cells(R, 1) = ""
CurY = Year(oSh.Cells(R, 1))
dSh.Cells(R + 1, 1).Offset(0, (CurY - MinY) * 2).End(xlUp).Offset(1, 0) = oSh.Cells(R, 1).Text
dSh.Cells(R + 1, 2).Offset(0, (CurY - MinY) * 2).End(xlUp).Offset(1, 0) = oSh.Cells(R, 2)
R = R + 1
Loop
End Sub |
Partager