[已解决]在總表內核對其他工作表的數據

happymary2

有活頁部內有多張工作表, 如何在首張工作表內, 核對每張工作表內的姓名與入職日期是否正確?
在首張工作表核對其他工作表數據.rar (25.33 KB, 下载次数: 5)

2015-8-11 23:37 上传

点击文件名下载附件

感謝!

最佳答案

月排行榜 / 总排行榜

dsmch

2015-8-12 05:45

1. Sub Macro1()
   
2. Dim arr, brr, crr, d, i&, j%
   
3. Set d = CreateObject("scripting.dictionary")
   
4. Sheets(1).Activate
   
5. arr = Range("a1").CurrentRegion
   
6. ReDim crr(1 To UBound(arr) - 1, 1 To 1)
   
7. For j = 2 To Sheets.Count
   
8.     brr = Sheets(j).Range("b2").CurrentRegion
   
9.     For i = 3 To UBound(brr)
   
10.         d(brr(i, 2)) = brr(i, 3)
    
11.     Next
    
12. Next
    
13. For i = 2 To UBound(arr)
    
14.     crr(i - 1, 1) = IIf(d(arr(i, 2)) = arr(i, 3), "OK", "X")
    
15. Next
    
16. Range("d2").Resize(UBound(crr)) = crr
    
17. End Sub

复制代码

跳转到最佳答案楼层

dsmch

1. Sub Macro1()
   
2. Dim arr, brr, crr, d, i&, j%
   
3. Set d = CreateObject("scripting.dictionary")
   
4. Sheets(1).Activate
   
5. arr = Range("a1").CurrentRegion
   
6. ReDim crr(1 To UBound(arr) - 1, 1 To 1)
   
7. For j = 2 To Sheets.Count
   
8.     brr = Sheets(j).Range("b2").CurrentRegion
   
9.     For i = 3 To UBound(brr)
   
10.         d(brr(i, 2)) = brr(i, 3)
    
11.     Next
    
12. Next
    
13. For i = 2 To UBound(arr)
    
14.     crr(i - 1, 1) = IIf(d(arr(i, 2)) = arr(i, 3), "OK", "X")
    
15. Next
    
16. Range("d2").Resize(UBound(crr)) = crr
    
17. End Sub

复制代码