|
本帖最后由 w2001pf 于 2013-10-25 09:32 编辑
尽管开贴了,还是改进一下,我的电脑上运行只比0.008左右。
Sub 格式转换()
Dim arr, arrj, i As Integer, l1 As Integer, l2 As Integer, l3 As Integer, n As Integer, m As Integer
Dim d As New Dictionary
Dim d1 As New Dictionary
t = Timer
arr = Sheets("数据源").Range("A2:G449")
For i = 1 To UBound(arr)
If d.Exists(arr(i, 1)) = False Then
d(arr(i, 1)) = arr(i, 2)
End If
Next i
ReDim arrj(1 To 3 * d.Count, 1 To 10) As String
For i = 1 To UBound(arr)
If d1.Exists(arr(i, 1)) = False Then
n = n + 1
d1(arr(i, 1)) = ""
l1 = 3: l2 = 3: l3 = 3
arrj(3 * (n - 1) + 1, 1) = arr(i, 1): arrj(3 * (n - 1) + 1, 2) = arr(i, 2)
arrj(3 * (n - 1) + 1, 3) = "CAP前": arrj(3 * (n - 1) + 2, 3) = "CAP后": arrj(3 * (n - 1) + 3, 3) = "完成品"
If arr(i, 5) <> "" Then l1 = l1 + 1: arrj(3 * (n - 1) + 1, l1) = arr(i, 3)
If arr(i, 6) <> "" Then l2 = l2 + 1: arrj(3 * (n - 1) + 2, l2) = arr(i, 3)
If arr(i, 7) <> "" Then l3 = l3 + 1: arrj(3 * (n - 1) + 3, l3) = arr(i, 3)
Else
If arr(i, 5) <> "" Then l1 = l1 + 1: arrj(3 * (n - 1) + 1, l1) = arr(i, 3)
If arr(i, 6) <> "" Then l2 = l2 + 1: arrj(3 * (n - 1) + 2, l2) = arr(i, 3)
If arr(i, 7) <> "" Then l3 = l3 + 1: arrj(3 * (n - 1) + 3, l3) = arr(i, 3)
End If
Next i
With Sheets("结果")
.Range("A1:J194") = ""
.Range("A2").Resize(UBound(arrj), 10) = arrj
End With
MsgBox Timer - t
End Sub
|
|