请教求取最大值和缺失的值

swl4561255

Sub tiqu()
Dim brr()
arr = Range("A2:A11")
mys = Join(Application.Transpose(arr), ",")
n = 0
For i = 1 To 20
If InStr("," & mys & ",", "," & i & ",") = 0 Then
   m = m + 1
   ReDim Preserve brr(1 To m)
   brr(m) = i
Else
   If i > n Then
      n = i
End If
End If
Next
[C2].Resize(m, 1) = Application.Transpose(brr)
[D2] = n
End Sub

swl4561255

1. Sub tiqu()
   
2. Dim brr()
   
3. arr = Range("A2:A11")
   
4. mys = Join(Application.Transpose(arr), ",")
   
5. n = 0
   
6. For i = 1 To 20
   
7. If InStr("," & mys & ",", "," & i & ",") = 0 Then
   
8.    m = m + 1
   
9.    ReDim Preserve brr(1 To m)
   
10.    brr(m) = i
    
11. Else
    
12.    If i > n Then
    
13.       n = i
    
14. End If
    
15. End If
    
16. Next
    
17. [C2].Resize(m, 1) = Application.Transpose(brr)
    
18. [D2] = n
    
19. End Sub

复制代码

grf1973

舍简就繁，练一下JSA的数组操作reduce,map,filter等。

准提部林

sub test_a1() \\by.准提部林
dim arr, a ,xd, i&, mx&, n&
Set xd = CreateObject("Scripting.Dictionary")
arr=range([a1],[a65536].end(3))
for i = 2 to ubound(arr)
    for each a in split(arr(i, 1), ",")
         if val(a) > mx then mx= a
         xd(val(a))=1
    next
next i
redim arr(1 to mx, 1): arr(1,1)=mx
for i=1 to mx
    if xd( i)=0 then n=n+1: arr(n,0)=i
next i
[b2].resize(n+1,2)=arr
end sub

saghj

交个作业。。