Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Probeer onze RefTreeAnalyser
de beste Excel formule auditing tool.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Transponeer Tabel
This page in English

De formules in een tabel transponeren

Recentelijk vroeg mij iemand of het mogelijk is om een tabel met cellen te transponeren, zodanig dat de formule van elke cel intact blijft. Ik besloot dat VBA de aangewezen weg was om dit te bereiken en heb een kleine maar handige subroutine gemaakt die dat doet.

Stel je onderstaand tabelletje voor:

Het is de bedoeling om dit ervan te maken (het geselecteerde bereik bevat het beoogde resultaat):

Als je dat probeert via Kopieren > Plakken Speciaal > Formules > Transponeren, dan is dit het resultaat:

Maar dat was niet de bedoeling!

Met het volgende stukje VBA code gaat deze operatie razendsnel, zonder de formules overhoop te halen:

Option Explicit

Sub TransposeFormulas()
    Dim vFormulas As Variant
    Dim oSel As Range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Selecteer eerst een bereik cellen.", _
                vbOKOnly + vbInformation, "Transponeer formules"
        Exit Sub
    End If
    Set oSel = Selection
    vFormulas = oSel.Formula
    vFormulas = Application.WorksheetFunction.Transpose(vFormulas)
    oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas
End Sub

Hoe werkt dit precies?

Eerst controleren we of de huidige selectie een celbereik is, want anders genereert de code een uitvoeringsfout:

    If TypeName(Selection) <> "Range" Then
        MsgBox "Selecteer eerst een bereik cellen.", _
                vbOKOnly + vbInformation, "Transponeer formules"
        Exit Sub
    End If

Vervolgens slaan we de geselecteerde cellen op in een object variabele (hetgeen niet noddzakelijk is), zodat we er in de code mee kunnen werken:

    Set oSel = Selection

Nu worden de formules van deze cellen in 1 klap in een variabele (van het type variant) gestopt :

    vFormulas = oSel.Formula

Als je door deze code heenstapt (met mijn voorbeeld cellen), dan toont het venster locale variabelen van de Visual Basic Editor (VBE) deze inhoud voor vFormulas na uitvoeren van bovenstaande regel:

Zoals je ziet is vFormulas een 4*3 matrix.

Vervolgens willen we de rijen en kolommen van deze matrix verwisselen. Hiervoor gebruik ik de werkblad functie Transponeren:

    vFormulas = Application.WorksheetFunction.Transpose(vFormulas)

De struktuur van vFormulas verandert naar:

Presto! Een 4 * 3 matrix!

Tenslotten moeten de formules terug naar het werkblad. Om dat te kunnen doen, moeten we een reeks cellen opgeven die exact dezelfde dimensies heeft als de formule matrix die we net hebben gemaakt. Ik heb daarvoor de resize methide gebruikt. Tevens zorg ik er via de Offset methode voor, dat de resulterende tabel onder het origineel komt te staan:

    oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas

Klaar!


Vragen, suggesties en opmerkingen

Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: www.eileenslounge.com.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].