|
加了注释。- Sub tt()
- Set d = CreateObject("scripting.dictionary")
- r = [b65536].End(3).Row
- [f3:f65536] = ""
- arr = Range("a1:f" & r)
- For i = 3 To UBound(arr)
- x = Trim(arr(i, 3))
- x = Replace(x, " ", "") '项目名称去空格作为key
- d(x) = d(x) & "," & i '相同项目名称所在的行数作为item
- Next
- dt = d.items
- For i = 1 To UBound(dt)
- xrr = Split(dt(i), ","): n = UBound(xrr) 'xrr为'相同项目名称所在的行数的数组
- If n > 1 Then 'n>1表示一个项目有2个以上行
- yrr = xrr '令yrr和xrr数组相同,以作比较
- For j = 1 To n - 1
- If xrr(j + 1) - xrr(j) = 1 Then yrr(j) = 0: yrr(j + 1) = 0 '如果行数相邻,yrr对应项设为0
- Next
- s = Join(yrr, ","): s = Replace(s, ",0", "") 'yrr字符串相连把,把0去掉,如果去掉后字符串为0,说明所有行数均相邻
- For j = 1 To n
- k = xrr(j)
- If Len(s) = 0 Then arr(k, 6) = "正常" Else arr(k, 6) = "重复:" & Mid(dt(i), 2) '如果相邻,输出正常,否则输出重复行数
- Next
- End If
- Next
- [f1].Resize(UBound(arr), 1) = Application.Index(arr, , 6)
- End Sub
复制代码 |
|