- Sub Macro1()
- Dim arr, brr, d, d2, i&, s&
- Set d = CreateObject("scripting.dictionary") '判断队号
- Set d2 = CreateObject("scripting.dictionary") '判断同一队号的数量
- arr = Sheet1.Range("a1").CurrentRegion '单元格赋值数组
- ReDim brr(1 To UBound(arr), 1 To 11)
- For i = 2 To UBound(arr) '数组循环
- d2(arr(i, 4)) = d2(arr(i, 4)) + 1 '同一队号数量累加
- If Not d.exists(arr(i, 4)) Then '如果队号不存在则
- s = s + 1 '序号
- d(arr(i, 4)) = s '序号队号对应
- brr(s, 1) = s '序号
- brr(s, 2) = arr(i, 2) '时间
- brr(s, 3) = arr(i, 4) '队号
- brr(s, 4) = arr(i, 5) '位置
- brr(s, 5) = d2(arr(i, 4)) & "." & arr(i, 13) '存在问题
- brr(s, 7) = d2(arr(i, 4)) & "." & arr(i, 14) '整改
- brr(s, 9) = arr(i, 8) '整改人
- brr(s, 11) = arr(i, 7) '检查人
- Else '如果队号存在,按其序号,分别对存在问题和整改方式连接字符串
- brr(d(arr(i, 4)), 5) = brr(d(arr(i, 4)), 5) & " " & d2(arr(i, 4)) & "." & arr(i, 13)
- brr(d(arr(i, 4)), 7) = brr(d(arr(i, 4)), 7) & " " & d2(arr(i, 4)) & "." & arr(i, 14)
- End If
- Next
- Range("a2").Resize(s, UBound(brr, 2)) = brr '数组赋值单元格
- End Sub
复制代码 |