- Sub aaa()
- Dim arr, brr, crr, i&, j&, k&, l&, r&, d As Object, n&
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets(1).[a1:f1]
- For i = 1 To UBound(arr, 2)
- d(arr(1, i)) = ""
- Next i
- brr = Sheets(2).UsedRange
- ReDim crr(1 To UBound(brr), 1 To UBound(brr, 2))
- For j = 1 To UBound(arr, 2)
- For i = 1 To UBound(brr)
- If arr(1, j) = brr(i, 2) Then
- For k = 2 To 7
- If d.exists(brr(i, k)) Then n = n + 1
- Next k
- If n = 4 Then
- r = r + 1
- For l = 1 To UBound(brr, 2)
- crr(r, l) = brr(i, l)
- Next l
- End If
- n = 0
- End If
- Next i
- r = r + 1
- Next j
- Sheets(3).Cells.Clear
- Sheets(3).[a1].Resize(r, UBound(crr, 2)) = crr
- End Sub
复制代码 |