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!