|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("sheet1")
- .Range("e5:e" & .Rows.Count).ClearContents
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a5:a" & r)
- For i = 1 To UBound(arr)
- Set d(i) = CreateObject("scripting.dictionary")
- xm = Split(arr(i, 1), Space(1))
- For j = 0 To UBound(xm)
- d(i)(xm(j)) = Empty
- Next
- Next
- r = .Cells(.Rows.Count, 3).End(xlUp).Row
- brr = .Range("c5:c" & r)
- For i = 1 To UBound(brr)
- brr(i, 1) = Split(Replace(Replace(brr(i, 1), "-", Space(1)), "=", Space(1)), Space(1))
- Next
- x = 4
- For k = 1 To UBound(brr) Step x
- For Each aa In d.keys
- m = 0
- For q = 1 To x
- n = 0
- For j = 2 To UBound(brr(k + q - 1, 1))
- If d(aa).exists(brr(k + q - 1, 1)(j)) Then
- n = n + 1
- End If
- Next
- If n >= Val(brr(k + q - 1, 1)(0)) And n <= Val(brr(k + q - 1, 1)(1)) Then
- m = m + 1
- End If
- Next
- If m <> x Then
- d.Remove (aa)
- End If
- Next
- Next
- If d.Count > 0 Then
- ReDim crr(1 To d.Count, 1 To 1)
- m = 0
- For Each aa In d.keys
- m = m + 1
- crr(m, 1) = Join(d(aa).keys, Space(1))
- Next
- .Range("e5").Resize(m, 1) = crr
- End If
- End With
- End Sub
复制代码 |
|