|
本帖最后由 乐乐2006201506 于 2016-5-20 16:42 编辑
将附件工作簿中工作表3中的姓名,在工作表1中找到并达到工作表2中的效果。如果找不到的姓名,用红色在工作表3中相应的单元格中标注。谢谢!
- Sub Macro1()
- Dim arr, brr, d, d2, i&
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Sheet1.Activate
- arr = Range("a6").CurrentRegion
- brr = Sheet3.Range("a1").CurrentRegion
- For i = 1 To UBound(arr)
- d2(arr(i, 2)) = ""
- Next
- For i = 1 To UBound(brr)
- If Not d2.exists(brr(i, 1)) Then Sheet3.Cells(i, 1).Interior.ColorIndex = 3
- d(brr(i, 1)) = ""
- Next
- For i = 1 To UBound(arr)
- If d.exists(arr(i, 2)) Then
- n = Range("a65536").End(xlUp).Row
- If Cells(n, 1) = arr(i, 1) - 1 Then n = n + 1 Else n = n + 2
- Cells(i + 5, 1).Resize(1, 2).Cut Cells(n, 1)
- End If
- Next
- Range("a6").Resize(UBound(arr), 2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
- End Sub
复制代码
|
|