variabele om aantal unieke tekstwaarden te tellen

[remember]

Wij zijn nieuw geregistreerden in dit forum maar hebben al zeer vaak zeer goede tips gevonden op deze site. Nu hebben we zelf een vraagje.

Voor het omzetten van een moeilijk leesbaar txt-bestand naar een xls-bestand hebben we een vrij goed werkende macro gemaakt. Deze is opgeslagen in het Personal.xlsb bestand in de map XLSTART (Excel 2010) om te kunnen worden herbruikt op verschillende txt-bestanden.

Eén deeltje krijgen we echter niet goed gedefinieerd.

Omdat we één waarde op meerdere plaatsen in onze code gebruiken, declareerden we een variabele als Integer met de bedoeling om daar het resultaat in te bewaren van een telling van het aantal UNIEKE tekstwaarden die zich in kolom A op een ander werkblad bevinden. Lege tekstwaarden en 0-waarden mogen niet worden meegeteld.
Er zijn maximum 8 mogelijke verschillende tekstwaarden, soms minder, soms maar 1, soms geen. Het resultaat zal dus steeds een getal zijn van 0 tot en met 8.
Het aantal keer dat die tekstwaarden voorkomen is onbelangrijk, ook de tekstwaarden zelf zijn niet relevant.
De range waarbinnen we willen tellen bevat een variërend aantal rijen (meestal meer dan 10000). Tussen de tekstwaarden zijn er ook rijen met lege cellen in kolom A.
De rijen kunnen niet eerst gesorteerd worden om de lege waarden er tussen uit te sorteren omdat er in de tussenliggende rijen nog gegevens staan in de andere kolommen en die moeten samen blijven in één blok (niet in bijgevoegd voorbeeld om bestand te verkleinen voor upload).

Als VBA leek proberen we dit te doen met een matrixformule die deze telling uitvoert, en die we hebben opgenomen in Excel 2010. Graag uw hulp om de syntax juist te krijgen om deze variabele een waarde te geven of voor een stukje code dat hetzelfde resultaat geeft.

Hierna de eerste lijnen van onze procedure, het probleem zit in de laatste variabele cnt

        Dim shName1 As String
        Dim shName2 As String
        Dim shName3 As String
        Dim htl As String
        Dim cnt As Integer
 
        shName1 = "roominglist"
        shName2 = "details per po"
        shName3 = "arrivals per date"
        htl = Range("'details per po'!$A$2")
        cnt = Selection.FormulaArray = _
        "=SUM(IF(FREQUENCY(IF(LEN(R[1]C[-10]:R[15000]C[-10])>0,MATCH(R[1]C[-10]:R[15000]C[-10],R[1]C[-10]:R[15000]C[-10],0),"""")," & _
        "IF(LEN(R[1]C[-10]:R[15000]C[-10])>0,MATCH(R[1]C[-10]:R[15000]C[-10],R[1]C[-10]:R[15000]C[-10],0),""""))>0,1))"

In bijlage een zeer klein voorbeeldje van een Excel 2010 bestandje waarop de code moet draaien. Normaal zitten er meer dan 10000 rijen in het tweede werkblad.

Hartelijk dank bij voorbaat voor uw gewaardeerde hulp.
Bekijk bijlage Roominglist - voorbeeld.xlsm

 

[Warme bakkertje]

Sub tst()
    Set dic = CreateObject("Scripting.Dictionary")
    With dic
        For Each cell In Sheets("details per po").Columns(1).SpecialCells(2)
            If Not .Exists(cell.Value) Then .Add cell.Value, 1
        Next cell
        vAantal = .Count - 1
    End With
    MsgBox vAantal
End Sub

 

[HSV]

Als je item neemt Rudi.

Sub tst()
Dim cell as range
    Set dic = CreateObject("Scripting.Dictionary")
    With dic
        For Each cell In Sheets("details per po").Columns(1).SpecialCells(2)
        .Item(cell.Value) = cell.Value
        Next cell
    MsgBox .Count - 1
   End With
End Sub

 

[remember]

Hartelijk dank beiden voor de hulp.
We gaan de oplossing met item inbouwen in de rest van onze code.

Weeral iets bijgeleerd.

Met vriendelijke groeten
Paul

 

[snb]

Sub M_snb()
    sn=Sheets("details per po").Columns(1).SpecialCells(2)

    with CreateObject("Scripting.Dictionary")
       For Each it in sn
            x0=.item(it)
       Next
       msgbox.Count
    End With
End Sub

 

[remember]

Oplossingen bij de vleet.

Hartelijk dank voor uw bijdrage.

mvg
Paul

 

[HSV]

@snb,

Jouw code geeft een twee retour terwijl het minus koptekst vier zijn.