在“转换格式”的A列(序号)前插入一列“日期”,然后执行代码即可:- Sub test()
- Dim sj(), k%, i%, p%, m%
- With Sheets("导出文件")
- nR = .[a65536].End(xlUp).Row
- If nR < 2 Then Exit Sub
- arr = .[a2].Resize(nR - 1, 40)
- End With
- ReDim sj(6, 0): k = -1
- For i = 1 To nR - 1
- For p = 1 To 6
- If arr(i, 5 * p) <> "" Or arr(i, 5 * p + 1) <> "" Then
- k = k + 1
- ReDim Preserve sj(6, k)
- sj(0, k) = DateSerial(Year(Date), arr(i, 39), arr(i, 40))
- sj(1, k) = arr(i, 1)
- For m = 1 To 5
- sj(m + 1, k) = arr(i, 5 * p + m - 4)
- Next
- End If
- Next
- Next
- If k < 0 Then Exit Sub
- With Sheets("转换格式")
- .[a2].Resize(65535, 7).ClearContents
- .[a2].Resize(k + 1, 7) = Application.Transpose(sj)
- End With
- End Sub
复制代码 |