Oorspronkelijk geplaatst op: 3 mei 2014

Soms wil je de celadressen  (bijvoorbeeld $D$2) weten waarin de naam van een item of tekenreeks voorkomt. Zie afbeelding voor de opzet.

Kolom A: Stad
Kolom B: Land
Kolom C: Adres

Nu wil je weten in welke celadressen het land Italy voorkomt.

In F2 komt de formule:

=ALS.FOUT(CEL(“adres”;INDEX($B$2:$B$94;KLEINSTE(ALS(ISGETAL(
VIND.ALLES(” “&F$1&” “;” “&$B$2:$B$94&” “));
RIJ($B$2:$B$94)-RIJ($B$2)+1);RIJEN($F$2:F2))));””)

Let op ! ! ! Dit is een Matrixformule en MOET ingevoerd worden met Ctrl+Shift+Enter dus NIET met ENTER.

Vervolgens formule naar beneden kopiëren bijvoorbeeld van F3 tot F10.

Om het gemakkelijk te maken heb ik in kolom H een lijst opgenomen met alle landennamen die voorkomen. Die lijst is met behulp van gegevensvalidatie gekoppeld aan cel F1. Onderstaand venster kun je bereiken d.m.v. 

Gegevens | Gegevensvalidatie | Instellingen

Bij “Toestaan” kies je voor “Lijst” en bij “Bron” geef je het bereik op dus: $H$2:$H$20

Oorspronkelijk geplaatst op: 3 mei 2014

Door het toepassen van voorwaardelijke opmaak kun je snel verschillen opmerken in een bereik met waarden. Wat moet je doen?

Selecteer de gegevens waarop je voorwaardelijke opmaak wil toepassen.

Vervolgens klik je op:
Voorwaardelijke opmaak | Markeerregels voor cellen | Tekst met

In het onderstaande vak kun je de naam van een land invoeren maar dat is niet handig want dat beperkt de boel tot dat land. Beter is om naar een cel te verwijzen in dit geval F2. Telkens als je nu de waarde in F2 verandert, verandert ook de kleurmarkering al naar gelang het land dat je invoert.
Let op ! ! ! In het rechtervak kun je nog een mooie opmaak kiezen. Experimenteren maar.

Uiteindelijk kun je dit als resultaat krijgen

Oorspronkelijk geplaatst op: 4 mei 2014

Het probleem is dat je dubbele waarden in kolom A hebt. In dit voorbeeld zijn dat groente en fruit in het bereik A4:A20.

Met de functie VERT.ZOEKEN kun je daarom niet goed uit de voeten. Echter, je kunt kolom A en Kolom B samenvoegen in je formule d.m.v. het & teken. Met de functies INDEX en VERGELIJKEN kun je toch de juiste waarden opzoeken.

In cel C3 de formule:

=ALS.FOUT(INDEX($G$4:$G$20;VERGELIJKEN(A4&B4;$E$4:$E$20&$F$4:$F$20;0));””)

Let op ! ! ! Dit is een Matrixformule en MOET ingevoerd worden met Ctrl+Shift+Enter dus NIET met ENTER.

P.s. Je kunt de kolommen natuurlijk ook verwisselen. B wordt A en A wordt B maar daar gaat het hier niet om.

Oorspronkelijk geplaatst op: 4 mei 2014

Eenvoudig voorbeeld van de functie KIEZEN.

Vroeger kreeg je een rapport met daarop cijfers en een omschrijving die bij dat cijfer hoorde. Je weet wel:
“Zeer slecht”, “Slecht”, “Ruim onvoldoende” etc. Hoe weet je nu bij welk cijfer de juiste omschrijving hoort? Door gebruik te maken van de functie KIEZEN kun je dat achterhalen.

De formule die we in cel F11 invoeren is:

=KIEZEN(E11;”Zeer slecht”;”Slecht”; “Ruim onvoldoende”;”Onvoldoende”;
“Twijfelachtig”;”Voldoende”;”Ruim voldoende”;”Goed”;”Zeer goed”;”Uitstekend”)

Vervolgens doorvoeren tot en met cel F20. Je krijgt dan zoiets. Cijfers in E11:E20.

Maar de werkelijk kracht van de functie KIEZEN zie je hieronder in combinatie met de functie VERT.ZOEKEN. De opzet. Je hebt 3 tabellen genaamd tabel1, tabel2 en tabel3.

Formule in cel C11:

=VERT.ZOEKEN(B11;KIEZEN(A11;tabel1;tabel2;tabel3);2;ONWAAR)

KIEZEN(A11;tabel1;tabel2;tabel3) betekent dat KIEZEN in kolom A kijkt, of meer specifiek in cel A11. In die kolom staan de nummers 1, 2 en 3. De functie kiest dus een getal. Dit getal wordt gebruikt om de respectievelijke tabel te kiezen. Vervolgens neemt VERT.ZOEKEN de waarde in kolom B of meer specifiek cel B11 en zoekt dat op in de tabel die KIEZEN heeft geselecteerd. De formule kun je doorvoeren tot C20.

Oorspronkelijk geplaatst op: 12 mei 2014

Probleem:

Gegevens vanaf [A2] in het volgende formaat:
Achternaam Voornaam Nummer

Bijvoorbeeld:
Smith John 12345678

Eindresultaat moet zijn:
[B2] = Nummer
[C2] = Initialen van Voornamen en vervolgens Achternaam.

Het eerste woord in [A2] is altijd de Achternaam. Het laatste gegeven is altijd het Nummer. Alle woorden tussen Achternaam en Nummer zijn Voornamen en vormen de initialen in [C2].

Bijvoorbeeld:
[A2] = Smith John James 12346578

Eindresultaat:
[B2]: 12345678
[C2]: J J Smith

Formule [B2]:

=RIGHT(A2;LEN(A2)-FIND(“¶”;SUBSTITUTE(A2;” “;”¶”;LEN(A2)-LEN(SUBSTITUTE(A2;” “;””)))))

Formule [C2]:

=CONCATENATE(MID(A2;FIND(” “;A2)+1;1);” “;MID(A2;FIND(” “;A2;FIND(” “;A2)+1)+1;1);” “;LEFT(A2;FIND(” “;A2)-1))

Dit is de mooie en flexibele oplossing.

De volgende VBA‑functie (werkt voor onbeperkt aantal voornamen)

In Excel: ALT + F11 → Insert → Module → plak dit:

Function InitialsAndLastName(fullname As String) As String
    Dim parts() As String
    Dim i As Integer
    Dim result As String
    
    parts = Split(fullname, " ")
    
    ' Last name is first element
    Dim lastname As String
    lastname = parts(0)
    
    ' Build initials from all middle names
    For i = 1 To UBound(parts) - 1
        result = result & Left(parts(i), 1) & " "
    Next i
    
    InitialsAndLastName = Trim(result & lastname)
End Function

Function InitialsAndLastName(fullname As String) As String
    Dim parts() As String
    Dim i As Integer
    Dim result As String
    
    parts = Split(fullname, " ")
    
    ' Last name is first element
    Dim lastname As String
    lastname = parts(0)
    
    ' Build initials from all middle names
    For i = 1 To UBound(parts) - 1
        result = result & Left(parts(i), 1) & " "
    Next i
    
    InitialsAndLastName = Trim(result & lastname)
End Function

Gebruiken in bijvoorbeeld C2: =InitialsAndLastName(A2)

Voor het extraheren van het nummer in B2:
=RIGHT(A2;LEN(A2)-FIND(“¶”;SUBSTITUTE(A2;” “;”¶”;LEN(A2)-LEN(SUBSTITUTE(A2;” “;””)))))

Die VBA functie is veelzijdiger en robuuster en werkt als er meerdere voornamen zijn.

Oorspronkelijk geplaatst op: 12 mei 2014

In een rij staan in diverse cellen dubbele waarden. We willen echter alleen unieke waarden hebben. De functie zorgt er voor dat die dubbele waarden worden genegeerd en het resultaat in één cel wordt gezet waarbij de waarden door een komma worden gescheiden.

Function Dubbels(Target As Range, q As Boolean) As String
Dim rngCell As Range
Dim Resultaat As String

Resultaat = Empty

For Each rngCell In Target
If Not rngCell = Empty Then
    If q = False Then
        If Resultaat = Empty Then
            Resultaat = rngCell
        Else
            Resultaat = Resultaat & ", " & rngCell
        End If
    Else
        If InStr(Resultaat, rngCell) = 0 Then
            If Resultaat = Empty Then
                Resultaat = rngCell
            Else
                Resultaat = Resultaat & ", " & rngCell
            End If
        End If
    End If
End If
Next rngCell

Dubbels = Resultaat
End Function

Function Dubbels(Target As Range, q As Boolean) As String
Dim rngCell As Range
Dim Resultaat As String

Resultaat = Empty

For Each rngCell In Target
If Not rngCell = Empty Then
    If q = False Then
        If Resultaat = Empty Then
            Resultaat = rngCell
        Else
            Resultaat = Resultaat & ", " & rngCell
        End If
    Else
        If InStr(Resultaat, rngCell) = 0 Then
            If Resultaat = Empty Then
                Resultaat = rngCell
            Else
                Resultaat = Resultaat & ", " & rngCell
            End If
        End If
    End If
End If
Next rngCell

Dubbels = Resultaat
End Function

Oorspronkelijk geplaatst op: 12 mei 2014

Er zijn veel manieren om bereiken te selecteren. Hier een aantal voorbeelden.

– Gegevens staan in kolom C, beginnen vanaf cel [C1] én zijn aaneengesloten.
Selecteren met deze code:

Range("C1", Range("C1").End(xlDown)).Select

Range("C1", Range("C1").End(xlDown)).Select

– Gegevens staan in kolom C, beginnen vanaf cel [C1] én zijn NIET aaneengesloten.
Selecteren met deze code:

Range("c1", Range("C1048576").End(xlUp)).Select

Range("c1", Range("C1048576").End(xlUp)).Select

– Gegevens staan in bereik A1:D10, zijn aaneengesloten én de actieve cel bevindt zich in dat bereik namelijk B4.
Alle cellen vanaf actieve cel (B4) naar beneden en naar rechts selecteren met deze code:

Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select

Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select

Oorspronkelijk geplaatst op: 13 mei 2014

In Excel kun je op drie manieren gegevens importeren uit een tekstbestand:

Je kunt het tekstbestand openen in Excel of je kunt een tekstbestand importeren als een extern gegevensbereik.
Gegevens | Van tekst

Je kunt gegevens van Excel exporteren naar een tekstbestand met de opdracht Opslaan als.

De twee, meest gebruikte indelingen voor tekstbestanden zijn:

Tekstbestanden met scheidingstekens (TXT-bestanden), waarin de afzonderlijke tekstvelden zijn gescheiden door een tab (ASCII-tekencode 009).
Tekstbestanden met door een komma of puntkomma gescheiden waarden (CSV-bestanden), waarin de afzonderlijke tekstvelden zijn gescheiden door een komma (,) of puntkomma(;).
Je kunt voor TXT- en CSV-bestanden een ander scheidingsteken kiezen, wat nodig kan zijn als je de gegevens op jouw manier wilt importeren of exporteren.

Let op ! ! !

Je kunt maximaal 1.048.576 rijen en 16.384 kolommen importeren of exporteren.

Je kunt een CSV bestand ook inlezen middels een macro:

Sub Lees_CSV_Bestand()
Dim lngBestandNr As Long, lngRij As Long, varRij As Variant
Dim strTotaal As String, strRijen() As String, strVeld() As String
 
    'Freefile geeft als resultaat een integer met het volgende
    'bestandsnummer dat voor de instructie Open kan worden gebruikt.
    lngBestandNr = FreeFile
    
    'Open het juiste bestand. Let op de locatie.
    Open "C:\temp\test.csv" For Binary As #lngBestandNr
    
    'De Space functie geeft als resultaat een Tekenreeks
    'die bestaat uit het opgegeven aantal spaties in LOF
    'LOF betekent Lenght Of File
    strTotaal = Space(LOF(lngBestandNr))
    
    'Leest gegevens
    Get #lngBestandNr, , strTotaal
    
    'Sluiten
    Close #lngBestandNr
    
    'Let op ! ! !
    'Gebruik Chr(10), Chr(13), vbCr, vbLf, vbCrLf, of vbNewLine
    'Al naar gelang waarmee de rij eindigt. Rijen worden gesplitst
    strRijen = Split(strTotaal, vbCr) '< < < hier bijv. vbCr
    
    'Foutafhandeling uitschakelen
    On Error Resume Next
    For Each varRij In strRijen
        
        'Let op ! ! ! de zogenaamde delimiter
        'Kan zijn , of ; De velden worden gesplitst
        strVeld = Split(varRij, ",") '< < < hier bijv. de komma
        lngRij = lngRij + 1
        
        'Gegevens wegschrijven naar cel
        Cells(lngRij, "A").Resize(, UBound(strVeld) + 1) = strVeld
    Next
    
    'Foutafhandeling weer inschakelen
    On Error GoTo 0
End Sub

Sub Lees_CSV_Bestand()
Dim lngBestandNr As Long, lngRij As Long, varRij As Variant
Dim strTotaal As String, strRijen() As String, strVeld() As String
 
    'Freefile geeft als resultaat een integer met het volgende
    'bestandsnummer dat voor de instructie Open kan worden gebruikt.
    lngBestandNr = FreeFile
    
    'Open het juiste bestand. Let op de locatie.
    Open "C:\temp\test.csv" For Binary As #lngBestandNr
    
    'De Space functie geeft als resultaat een Tekenreeks
    'die bestaat uit het opgegeven aantal spaties in LOF
    'LOF betekent Lenght Of File
    strTotaal = Space(LOF(lngBestandNr))
    
    'Leest gegevens
    Get #lngBestandNr, , strTotaal
    
    'Sluiten
    Close #lngBestandNr
    
    'Let op ! ! !
    'Gebruik Chr(10), Chr(13), vbCr, vbLf, vbCrLf, of vbNewLine
    'Al naar gelang waarmee de rij eindigt. Rijen worden gesplitst
    strRijen = Split(strTotaal, vbCr) '< < < hier bijv. vbCr
    
    'Foutafhandeling uitschakelen
    On Error Resume Next
    For Each varRij In strRijen
        
        'Let op ! ! ! de zogenaamde delimiter
        'Kan zijn , of ; De velden worden gesplitst
        strVeld = Split(varRij, ",") '< < < hier bijv. de komma
        lngRij = lngRij + 1
        
        'Gegevens wegschrijven naar cel
        Cells(lngRij, "A").Resize(, UBound(strVeld) + 1) = strVeld
    Next
    
    'Foutafhandeling weer inschakelen
    On Error GoTo 0
End Sub

Test bestand:
https://www.dropbox.com/s/wmtt5rn3kl1j4sw/test.csv

Oorspronkelijk geplaatst op: 13 mei 2014

Je wilt rijen verwijderen waarvan de kolommen B, C én D géén gegevens bevatten.

De volgende code zorgt daarvoor. In dit voorbeeld worden alleen de rijen 3 en 7 verwijderd want die bevatten in én kolom B én C én D geen gegevens.

Sub Verwijder_Rijen_Indien_Geen_Waarde_In_Kolommen_B_C_D()
Dim Laatste_Rij As Long
    Laatste_Rij = Cells(Rows.Count, "A").End(xlUp).Row
    Range("B1:B" & Laatste_Rij) = Evaluate(Replace _
    ("IF(B1:B@&C1:C@&D1:D@="""",""#N/A"",IF(B1:B@="""","""",B1:B@))", "@", Laatste_Rij))
    On Error Resume Next
    Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    If Err.Number = 1004 Then
        MsgBox "Er zijn geen rijen gevonden" & Chr(13) _
        & "die aan de criteria voldoen!"
    End If
    On Error GoTo 0
    Err.Clear
End Sub

Sub Verwijder_Rijen_Indien_Geen_Waarde_In_Kolommen_B_C_D()
Dim Laatste_Rij As Long
    Laatste_Rij = Cells(Rows.Count, "A").End(xlUp).Row
    Range("B1:B" & Laatste_Rij) = Evaluate(Replace _
    ("IF(B1:B@&C1:C@&D1:D@="""",""#N/A"",IF(B1:B@="""","""",B1:B@))", "@", Laatste_Rij))
    On Error Resume Next
    Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    If Err.Number = 1004 Then
        MsgBox "Er zijn geen rijen gevonden" & Chr(13) _
        & "die aan de criteria voldoen!"
    End If
    On Error GoTo 0
    Err.Clear
End Sub

Oorspronkelijk geplaatst op: 15 mei 2014

Het probleem is het volgende.
Er staan gegevens in een cel en de opmaak van de cel staat op tekst terugloop ingesteld. Hierdoor staan er meerdere regels gegevens in 1 cel. Deze gegevens wil je splitsen naar andere cellen. Dit is een voorbeeld van de situatie:

Sla het bestand op als temp.csv, in de lokatie:
C:\temp\temp.csv
Let op ! ! ! Bestandsextensie is dus .csv.
Je krijgt dan het volgende bestand als je dit opent in de gratis te downloaden tekstverwerker Notepad++

Neem onderstaande code op in een nieuwe werkmap

Let op ! ! ! TEST eerst in een kopie van je werkmap. Je weet nooit of het mis gaat en dan ben je je gegevens kwijt
1. Kopieer de onderstaande code
2. Open een nieuwe werkmap
3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
5. Druk op M om een standaard module in te voegen
6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
8. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbeklik op de macro naam om te starten.

Sub Lees_CSV_Bestand()
Dim lngBestandNr As Long, lngRij As Long, varRij As Variant
Dim strTotaal As String, strRijen() As String, strVeld() As String
 
 
    'Freefile gives an integer
    lngBestandNr = FreeFile
    
    'Open the right file, watch location ! ! !
    Open "C:\temp\temp.csv" For Binary As #lngBestandNr
    
    'Space function gives a string with spaces.
    strTotaal = Space(LOF(lngBestandNr))
    
    'Read the dataset
    Get #lngBestandNr, , strTotaal
    
    'Close
    Close #lngBestandNr
    
    'Pay attention ! ! !
    'Use Chr(10), Chr(13), vbCr, vbLf, vbCrLf, of vbNewLine
    'Depends what character is used for end of line
    strRijen = Split(strTotaal, vbCrLf) '< < < here use vbCrLf just like in the screen above
    
    'Error handling off
    On Error Resume Next
    lngRij = 1
    x = 1
    
    For Each varRij In strRijen
        'Write data to worksheet cells.
        Cells(lngRij, x) = varRij 'varRij.Resize(, UBound(strVeld) + 1) = strVeld
        x = x + 1
        If x = 7 Then
            x = 1
            lngRij = lngRij + 1
        End If
    Next
    
    'Error handling on.
    On Error GoTo 0
End Sub

Sub Lees_CSV_Bestand()
Dim lngBestandNr As Long, lngRij As Long, varRij As Variant
Dim strTotaal As String, strRijen() As String, strVeld() As String
 
 
    'Freefile gives an integer
    lngBestandNr = FreeFile
    
    'Open the right file, watch location ! ! !
    Open "C:\temp\temp.csv" For Binary As #lngBestandNr
    
    'Space function gives a string with spaces.
    strTotaal = Space(LOF(lngBestandNr))
    
    'Read the dataset
    Get #lngBestandNr, , strTotaal
    
    'Close
    Close #lngBestandNr
    
    'Pay attention ! ! !
    'Use Chr(10), Chr(13), vbCr, vbLf, vbCrLf, of vbNewLine
    'Depends what character is used for end of line
    strRijen = Split(strTotaal, vbCrLf) '< < < here use vbCrLf just like in the screen above
    
    'Error handling off
    On Error Resume Next
    lngRij = 1
    x = 1
    
    For Each varRij In strRijen
        'Write data to worksheet cells.
        Cells(lngRij, x) = varRij 'varRij.Resize(, UBound(strVeld) + 1) = strVeld
        x = x + 1
        If x = 7 Then
            x = 1
            lngRij = lngRij + 1
        End If
    Next
    
    'Error handling on.
    On Error GoTo 0
End Sub

En je krijgt dit als resultaat. De gegevens staan nu in de cellen A1:F1: