- Sub test()
- Dim arr, brr(), crr(1 To 50, 1 To 3), drr, d1, d2
- Dim i As Long, j As Long, k As Long, x As Long, y As Long, irow As Long
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- irow = 29 '提定的行
- For i = 2 To Worksheets.Count
- With Sheets(i)
- drr = .Cells(1, 1).Resize(50, 149)
- arr = .Cells(irow, 1).Resize(1, 149)
- d1.RemoveAll
- d2.RemoveAll
- Erase brr, crr
- k = 0
- For x = 51 To 99
- d1(arr(1, x)) = ""
- Next
- For x = 101 To 149
- d2(arr(1, x)) = ""
- Next
- For x = 1 To 49
- If d1.exists(arr(1, x)) And d2.exists(arr(1, x)) Then
- k = k + 1
- ReDim Preserve brr(1 To k)
- brr(k) = arr(1, x)
- If k = 3 Then Exit For
- End If
- Next
- For x = 1 To 3
- For y = x * 50 - 49 To x * 50 - 1
- If arr(1, y) = brr(x) Then
- For j = 1 To 50
- crr(j, x) = drr(j, y)
- Next
- Exit For
- End If
- Next
- Next
- .Cells(1, 151).Resize(50, 3) = crr
- End With
- Next
- Set d1 = Nothing
- Set d2 = Nothing
- End Sub
复制代码 |