- Sub lqxs()
- Dim Arr, i&, Arr1, Arr2, j&, aa
- Dim d, k, t
- Set d = CreateObject("Scripting.Dictionary")
- Sheet2.Activate
- Arr = [a1].CurrentRegion
- Arr1 = [a1].Resize(UBound(Arr), 7)
- Arr2 = [h1].Resize(UBound(Arr), 3)
- For i = 2 To UBound(Arr)
- d(Arr(i, 1)) = d(Arr(i, 1)) & i & ","
- Next
- For i = 2 To UBound(Arr)
- If d.exists(Arr(i, 8)) Then
- t = d(Arr(i, 8))
- t = Left(t, Len(t) - 1)
- If InStr(t, ",") Then
- aa = Split(t, ",")
- For j = 0 To UBound(aa)
- If Arr(aa(j), 10) = Arr(i, 10) Then
- With Sheet4
- n = .[a65536].End(xlUp).Row + 1
- .Cells(n, 1).Resize(1, 7) = Application.Index(Arr1, aa(j), 0)
- .Cells(n, 8).Resize(1, 3) = Application.Index(Arr2, i, 0)
- End With
- End If
- Next
- Else
- If Arr(t, 10) = Arr(i, 10) Then
- With Sheet4
- n = .[a65536].End(xlUp).Row + 1
- .Cells(n, 1).Resize(1, 7) = Application.Index(Arr1, t, 0)
- .Cells(n, 8).Resize(1, 3) = Application.Index(Arr2, i, 0)
- End With
- End If
- End If
- End If
- Next
- End Sub
复制代码 |