|
Sub dd()
c = Sheets("用料表").Range("b655306").End(xlUp).Row
n = 1
For a = 2 To 18
For b = 2 To c
If Sheets("sheet1").Cells(a, 4) = Sheets("用料表").Cells(b, 2) Then
n = n + 1
For d = 1 To 5
Sheets("sheet2").Cells(n, d) = Sheets("用料表").Cells(b, d)
Next
f = 1
For e = 6 To 7
Sheets("sheet2").Cells(n, e) = Sheets("sheet1").Cells(a, f)
f = f + 1
Next
End If
Next
Next
End Sub
我是新手,这个代码数据量大的时候执行起来很慢。我想有更高效的代码,求帮忙!
本帖最后由 大灰狼1976 于 2017-8-4 09:20 编辑
- Sub aaa()
- Dim arr, brr, crr(1 To 10000, 1 To 7), drr, i&, j&, k&, d As Object, r&
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets(2).[a1].CurrentRegion
- For i = 2 To UBound(arr)
- d(arr(i, 2)) = d(arr(i, 2)) & "," & i
- Next i
- brr = Sheets(1).[a1].CurrentRegion
- For i = 2 To UBound(brr)
- drr = Split(d(brr(i, 4)), ",")
- For k = 1 To UBound(drr)
- r = r + 1
- For j = 1 To 5
- crr(r, j) = arr(drr(k), j)
- Next j
- crr(r, 6) = brr(i, 1)
- crr(r, 7) = brr(i, 2)
- Next k
- Next i
- Sheets(3).[a1].CurrentRegion.Offset(1).ClearContents
- Sheets(3).[a2].Resize(r, 7) = crr
- End Sub
复制代码
|
|