[已解决]如何找到本列没有的数呢？

westwenwei

如何找到本列没有的数呢？

最佳答案

月排行榜 / 总排行榜

大灰狼1976

2017-5-6 17:16

1. Sub aaa()
   
2. Dim arr, brr, i&, j&, k&, d As Object
   
3. Application.ScreenUpdating = False
   
4. Set d = CreateObject("scripting.dictionary")
   
5. arr = [a21].CurrentRegion
   
6. For i = 1 To UBound(arr, 2)
   
7.   Cells(21, i).Resize(UBound(arr)).RemoveDuplicates Columns:=1
   
8. Next i
   
9. brr = [a21].CurrentRegion
   
10. For j = 1 To UBound(brr, 2)
    
11.   For k = 0 To 999
    
12.     d(Replace(Format(k, "000"), 0, "o")) = ""
    
13.   Next k
    
14.   For i = 1 To UBound(brr)
    
15.     If brr(i, j) = "" Then Exit For
    
16.     If d.exists(CStr(brr(i, j))) Then d.Remove CStr(brr(i, j))
    
17.   Next i
    
18.   If d.Count > 0 Then Cells(1, j).Resize(d.Count) = Application.Transpose(d.keys)
    
19.   d.RemoveAll
    
20. Next j
    
21. [a21].Resize(UBound(arr), UBound(arr, 2)) = arr
    
22. Application.ScreenUpdating = True
    
23. End Sub

复制代码

跳转到最佳答案楼层

大灰狼1976

1. Sub aaa()
   
2. Dim arr, brr, i&, j&, k&, d As Object
   
3. Application.ScreenUpdating = False
   
4. Set d = CreateObject("scripting.dictionary")
   
5. arr = [a21].CurrentRegion
   
6. For i = 1 To UBound(arr, 2)
   
7.   Cells(21, i).Resize(UBound(arr)).RemoveDuplicates Columns:=1
   
8. Next i
   
9. brr = [a21].CurrentRegion
   
10. For j = 1 To UBound(brr, 2)
    
11.   For k = 0 To 999
    
12.     d(Replace(Format(k, "000"), 0, "o")) = ""
    
13.   Next k
    
14.   For i = 1 To UBound(brr)
    
15.     If brr(i, j) = "" Then Exit For
    
16.     If d.exists(CStr(brr(i, j))) Then d.Remove CStr(brr(i, j))
    
17.   Next i
    
18.   If d.Count > 0 Then Cells(1, j).Resize(d.Count) = Application.Transpose(d.keys)
    
19.   d.RemoveAll
    
20. Next j
    
21. [a21].Resize(UBound(arr), UBound(arr, 2)) = arr
    
22. Application.ScreenUpdating = True
    
23. End Sub

复制代码

westwenwei

大灰狼1976 发表于 2017-5-6 17:16

谢谢！非常感谢！！！