|
发表于 2017-8-25 16:35
|
显示全部楼层
本楼为最佳答案
不考虑同一天有相同料号单价时,以下代码。
- Sub aaa()
- Dim arr, brr, i&, d As Object, s$
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets(1).[a1].CurrentRegion
- For i = 2 To UBound(arr)
- s = arr(i, 2) & "," & arr(i, 3)
- If Not d.exists(s) Then d(s) = Array(arr(i, 4), arr(i, 1))
- If d(s)(1) < arr(i, 1) Then
- brr = d(s)
- brr(0) = arr(i, 4)
- brr(1) = arr(i, 1)
- d(s) = brr
- End If
- Next i
- arr = Sheets(2).Range("a2:b" & Sheets(2).Cells(Rows.Count, 1).End(3).Row)
- ReDim Preserve arr(1 To UBound(arr), 1 To 3)
- For i = 1 To UBound(arr)
- arr(i, 3) = d(arr(i, 1) & "," & arr(i, 2))(0)
- Next i
- Sheets(2).[c2].Resize(UBound(arr)) = Application.Index(arr, , 3)
- End Sub
复制代码 |
|