|
- Sub test()
- Dim d As Object
- Dim r%, i%
- Dim arr, brr
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 2).End(xlUp).Row
- arr = .Range("a2:n" & r)
- End With
- For i = 1 To UBound(arr)
- If Left(arr(i, 3), 1) Like "[!一-龥]" Then
- xm = arr(i, 1) & "+" & arr(i, 2)
- If Not d.Exists(xm) Then
- ReDim brr(1 To 4)
- brr(1) = arr(i, 3)
- brr(2) = arr(i, 4)
- brr(3) = arr(i, 10)
- brr(4) = arr(i, 7)
- Else
- brr = d(xm)
- If arr(i, 10) > brr(3) Then
- brr(3) = arr(i, 10)
- brr(4) = arr(i, 7)
- End If
- End If
- d(xm) = brr
- End If
- Next
- With Worksheets("sheet2")
- .Rows("4:" & .Rows.Count).ClearContents
- .Range("a:b,d:d").NumberFormatLocal = "@"
- .Range("a4").Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.Items))
- End With
- End Sub
复制代码 |
|