Macro: Wat is eerste getal dat in tabel ontbreekt?

[keb]

Ik heb een kolom in Excel met willekeurige getallen, meestal een getal per cel, maar soms meer getallen in één cel gescheiden door komma.
Ik ben op zoek naar het eerste getal dat ontbreekt en vervolgens toont.

Onderstaande macro met for next loop werkt wel, maar is erg traag. Ben daarom op zoek naar snelle methode (het gaat om een heel lange kolom). Het resultaat moet 8 zijn.

Sub Zoek_ontbrekendgetal()

Dim n  As Long, StartTime As Double, TimeElapsed As Double
Dim Found As Range

StartTime = Timer

For n = 2 To 20
    Set Found = Columns(1).Find(n, , xlValues, xlPart)
    If Found Is Nothing Then
        TimeElapsed = Round(Timer - StartTime, 2)
        MsgBox "Eerst ontbrekende getal is " & n & vbCrLf & " Time elapsed = " & TimeElapsed
        Exit Sub
        End If
    Next

end sub

 

[VenA]

Er staat geen macro in een .xlsx. En wat heeft de timer er mee te maken?

 

[HSV]

Onderstaande macro met for next loop werkt wel, Het resultaat moet 8 zijn.

Met xlPart kan het nooit werken.
Maak van die 2 maar eens 20 en het resultaat is nog steeds 8 i.p.v. 2.

 

[VenA]

De 8 ontbreekt inderdaad.

Sub VenA()
  ar = Sheets("Blad1").Cells(1).CurrentRegion.Columns(1)
  Set a = CreateObject("System.Collections.ArrayList")
    For j = 2 To UBound(ar)
      For jj = 0 To UBound(Split(ar(j, 1), ","))
        t = Val(Split(ar(j, 1), ",")(jj))
        a.Add t
      Next jj
    Next j
    a.Sort
End Sub

 

[mastermindzh]

Ik heb het topic maar even opgeschoond, toekomstige reactie's a.u.b inhoudelijk houden.

@TS, u heeft in alle opwelling nog vergeten de vraag in bericht #2 te beantwoorden

Met vriendelijk groet,
Rick van Lieshout

 

[HSV]

Met een toevoeging kom je daar wel achter.

Sub VenA_hsv()
  ar = Sheets("Blad1").Cells(1).CurrentRegion.Columns(1)
  Set a = CreateObject("System.Collections.ArrayList")
    For j = 2 To UBound(ar)
      For jj = 0 To UBound(Split(ar(j, 1), ","))
        t = Val(Split(ar(j, 1), ",")(jj))
        a.Add t
      Next jj
    Next j
    a.Sort
[COLOR=#0000FF]   For i = 0 To UBound(Split(Join(a.toarray)))[/COLOR]
[COLOR=#0000FF]     n = n + 1[/COLOR]
[COLOR=#0000FF]       If a(i) <> n Then[/COLOR]
[COLOR=#0000FF]         MsgBox "Eerst ontbrekende getal is " & n[/COLOR]
[COLOR=#0000FF]         Exit For[/COLOR]
[COLOR=#0000FF]       End If[/COLOR]
[COLOR=#0000FF]    Next i[/COLOR]
End Sub

Als bovenstaande niet werkt (bv. 64bit), dan onderstaande als alternatief.

Sub hsv()
Dim sv, sq, tmp, i As Long, ii As Long, j As Long, jj As Long, n As Long, x As Long
sv = Cells(1).CurrentRegion
ReDim arr(0)
For i = 2 To UBound(sv)
 sq = Split(sv(i, 1), ",")
   For j = 0 To UBound(sq)
        arr(n) = Val(sq(j))
          For jj = 0 To UBound(arr)
           If arr(jj) > arr(n) Then
             tmp = arr(jj)
             arr(jj) = arr(n)
             arr(n) = tmp
            End If
          Next jj
        n = n + 1
      ReDim Preserve arr(n)
   Next j
Next i
  For ii = 0 To UBound(arr) - 1
     x = x + 1
       If arr(ii) <> x Then
         MsgBox "Eerst ontbrekende getal is " & x
         Exit Sub
       End If
  Next ii
End Sub

 

[keb]

#Harry , bedankt voor jouw bijdrage.

Om er van te leren, heb ik jouw code als voorbeeld genomen voor mijn eigen code.
Ik weet helaas te weinig van arraylisten (ik begrijp dat je van alles en nog wat, getallen/tekst etc in verschillende kolommen als het ware in een cel kunt zette).

Inmiddels ben ik er wel in geslaagd om om een heel snelle manier getallen uit een range in een gesorteerde arraylist te zetten.
Helaas slaag ik er nog niet in om het eerst ontbrekende getal uit die arraylist te bepalen (zie step 3 uit mijn voorbeeld).

Ik ben benieuwd of je ziet waar mijn fout(en) zit(ten).
Ps. Ik gebruik Win10, 64 bits en Excel 2007.

 

[HSV]

Wat bedoel je met: Step 3.

 

[keb]

Step 3 uit de macro

 

[HSV]

Dit bedoel je?

Haal in ieder geval de rode coderegel uit de code.

'3  Check for the first missing number
    n = 10000
    For i = 0 To UBound(Split(Join(arrtemp.toarray)))
    n = n + 1
     [COLOR="#FF0000"]  MsgBox arrtemp(1)[/COLOR]
       If arrtemp(i) <> n Then
        
         MsgBox "Eerst ontbrekende plaatje is " & n
         Exit For
       End If
    Next i

 

[sylvester-ponte]

ik ben een beetje benieuwd.
moet de serie altijd met 1 beginnen?
kunnen er ook dubbelen in de serie zitten?