'结果对不上啊,,,
Option Explicit
Sub test()
Dim arr, brr, i, j, p, dic, s
Set dic = CreateObject("scripting.dictionary")
arr = Sheets("条件").[a1].CurrentRegion.Offset(1).Resize(, 4)
For i = 1 To UBound(arr, 1) - 1
arr(i, 4) = (Replace(arr(i, 3), "≥", vbNullString))
Next
Call bsort(arr, 1, UBound(arr, 1) - 1, 1, 4, 1)
For i = 1 To UBound(arr, 1) - 1
If arr(i, 1) <> arr(i + 1, 1) Then
dic(arr(i, 1)) = Array(p + 1, i - p)
Call bsort(arr, p + 1, i, 1, 4, 4)
p = i
End If
Next
With Sheets("不良明细")
brr = .Range("d2:q" & .[d2].End(xlDown).Row)
For i = 1 To UBound(brr, 1)
If dic.exists(brr(i, 1)) Then
s = vbNullString
For j = dic(brr(i, 1))(0) To dic(brr(i, 1))(0) + dic(brr(i, 1))(1) - 1
If brr(i, 12) >= Val(arr(j, 4)) Then
s = s & "," & arr(j, 2)
End If
Next
If Len(s) Then brr(i, 1) = Mid(s, 2) Else brr(i, 1) = vbNullString
Else
brr(i, 1) = vbNullString
End If
Next
.[t2].Resize(UBound(brr, 1)) = brr
End With
End Sub
Function bsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If arr(j, key) > arr(j + 1, key) Then
For k = left To right
t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
Next
End If
Next
Next
End Function |