|
发表于 2015-4-23 21:06
|
显示全部楼层
本楼为最佳答案
- Sub test()
- Dim Dic As Object, Dic1 As Object, Dic2 As Object
- Dim arr, brr, crr, drr
- Dim Iar As Integer, Ibr As Integer, Icount As Integer, _
- Icr As Integer, Idr As Integer, i As Integer
- Set Dic = CreateObject("scripting.dictionary")
- Set Dic1 = CreateObject("scripting.dictionary")
- Set Dic2 = CreateObject("scripting.dictionary")
- arr = Range("e5:e" & Cells(Rows.Count, 5).End(3).Row)
- For Iar = 1 To UBound(arr)
- Dic(arr(Iar, 1)) = ""
- Next
- brr = Range("i5:i" & Cells(Rows.Count, 5).End(3).Row)
- For Ibr = 1 To UBound(brr)
- If brr(Ibr, 1) <> "" Then
- Icount = Icount + 1
- End If
- Next
- crr = Range("c5:c" & Cells(Rows.Count, 5).End(3).Row)
- For Icr = 1 To UBound(brr)
- Dic1(crr(Icr, 1)) = ""
- Next
- drr = Range("d5:d" & Cells(Rows.Count, 5).End(3).Row)
- For Idr = 1 To UBound(brr)
- Dic2(drr(Idr, 1)) = ""
- Next
- Sheets("Sheet3").Cells(Rows.Count, "g").End(3).Offset(1, 0) = Dic.Count
- Sheets("Sheet3").Cells(Rows.Count, "f").End(3).Offset(1, 0) = Icount
- Sheets("Sheet3").Cells(Rows.Count, "d").End(3).Offset(1, 0).Resize(Dic1.Count) = Dic1.keys
- Sheets("Sheet3").Cells(Rows.Count, "e").End(3).Offset(1, 0).Resize(Dic2.Count) = Dic2.keys
- For i = 1 To Sheets("Sheet3").Cells(Rows.Count, 5).End(3).Row
- If Sheets("Sheet3").Cells(i, 6) > Sheet3.[b1] Then
- Sheets("Sheet3").Cells(i, 8) = Sheet3.[b1]
- Else
- Sheets("Sheet3").Cells(i, 8) = Sheet3.[b1] - 1
- End If
- Next i
- End Sub
复制代码 直接放进去就行了啊,但是我不知道你具体的规则,所以只有猜了 |
|