|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
修改如下:
Sub Adele()
Dim d As Object, sht As Worksheet
Set d = CreateObject("scripting.dictionary")
For Each sht In Worksheets
If sht.Name <> "TEST1" Then
With sht
arr = .Range("a1").CurrentRegion
For x = 2 To UBound(arr)
s = sht.Name & "," & arr(x, 1)
If UBound(arr, 2) > 11 Then
d(s) = Array(arr(x, 2), arr(x, 3), arr(x, 4), arr(x, 5), arr(x, 6), arr(x, 7), arr(x, 8), arr(x, 9), arr(x, 10), arr(x, 11), arr(x, 12))
Else
d(s) = Array(arr(x, 2), arr(x, 3), arr(x, 4), arr(x, 5), arr(x, 6), arr(x, 7), arr(x, 8), arr(x, 9))
End If
Next
End With
End If
Next
With Sheets("TEST1")
.Range("e2:q65535").ClearContents
brr = .Range("a1").CurrentRegion
For y = 2 To UBound(brr)
sbr = brr(y, 1) & "," & brr(y, 2)
If d.exists(sbr) Then
.Cells(y, 5).Resize(1, UBound(d(sbr)) + 1) = d(sbr)
End If
Next
End With
End Sub |
|