学习一下字典,打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
If Not d.exists(arr(i, 2)) Then
d(arr(i, 2)) = i
Else
r = d(arr(i, 2))
If arr(i, 7) < arr(r, 7) Then
d(arr(i, 2)) = i
End If
End If
Next
ReDim brr(1 To d.Count, 1 To 3)
For Each kk In d.keys
m = m + 1
brr(m, 1) = arr(d(kk), 2)
brr(m, 2) = arr(d(kk), 3)
brr(m, 3) = arr(d(kk), 1)
Next
[b30].Resize(, 3) = Array("物料代码", "物料名称", "仓库")
[b30].CurrentRegion.Offset(1).ClearContents
[b31].Resize(m, 3) = brr
End Sub |