VBA transponeren

Als gebruiker van Microsoft Excel hebt u vaak de transpose-functie gebruikt waarmee u tussen rijen en kolommen voor een array kunt wisselen. Rijen omzetten in kolommen en kolommen in rijen is wat een transponeerfunctie voor u doet in Excel. Het aantal rijen wordt een aantal kolommen en vice versa. Dit betekent dat als u 2 rijen en 3 kolommen in uw array hebt, deze na omzetting wordt gewijzigd in een array met 3 rijen en 2 kolommen. In deze zelfstudie doorlopen we VBA Transpose waarmee u de transpose-methode kunt automatiseren die u in Excel wilt gebruiken.

Syntaxis van transponeren in Excel VBA

VBA Transpose heeft de volgende syntaxis:

Waar,

Arg1 : Het is een verplicht argument dat niets anders is dan een reeks cellen die we wilden transponeren (dwz array).

Het eerste deel van de syntaxis is niets anders dan een uitdrukking waaronder de functie Transpose kan worden gebruikt. Bijvoorbeeld WorksheetFunction.

Hoe Excel VBA Transpose gebruiken?

We zullen leren hoe een Transpose te gebruiken met enkele voorbeelden in Excel VBA.

U kunt deze VBA Transpose Excel-sjabloon hier downloaden - VBA Transpose Excel-sjabloon

Voorbeeld # 1 - VBA-transpositie van eendimensionale array

Stel dat u werkt aan gegevens met lijsten (wat een eendimensionale array is) zoals de namen van een gegeven medewerker ("Lalit", "Sneha", "Ethyl", "John", "Cory") en u wilt deze lijst om op celniveau te worden geplakt. Laten we kijken hoe we dit kunnen doen.

Volg de onderstaande stappen om Transpose te gebruiken in VBA.

Stap 1: voeg een nieuwe module in en definieer een nieuwe subprocedure om een macro in VBA te maken.

Code:

 Sub Trans_ex1 () Einde Sub

Stap 2: Definieer een nieuwe variabele die uw eendimensionale array kan bevatten.

Code:

 Sub Trans_ex1 () Dim Arr1 As Variant End Sub

Stap 3: Definieer de lijst als een array met de functie Array. Dit zou de lijst zijn die u in uw Excel-blad wilde plakken.

Code:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") End Sub

Stap 4: Gebruik nu de methode Range.Value om het bereik te definiëren waarin u deze waarden wilt plakken.

Code:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Bereik ("A1: A5"). Waarde = Einde Sub

Stap 5: Gebruik Application.WorksheetFunction.Transpose op de gegeven array om de lijst te kunnen omzetten die is gedefinieerd onder variabele Arr1.

Code:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Bereik ("A1: A5"). Value = Application.WorksheetFunction.Transpose (Arr1 ) Einde Sub

Hier hebben we een code gedefinieerd waarmee het systeem de gegevens in lijstarray genaamd Arr1 kan transponeren en vervolgens onder cel A1: A5 op het actieve werkblad opslaan.

Stap 6: Druk op F5 of op de knop Uitvoeren onder VBE om deze code uit te voeren en de uitvoer te bekijken onder een actieve Excel-spreadsheet.

U kunt zien dat de gegeven reeks van de lijst in één enkele kolom is omgezet en onder cel A1 tot A5 is opgeslagen.

Voorbeeld # 2 - VBA-transpositie van tweedimensionale array

Stel dat u een tweedimensionale reeks werknemers en hun salarissen hebt, zoals hieronder:

Dit zijn tweedimensionale gegevens met zes rijen en twee kolommen. Na transponeren zou het een array zijn met twee rijen en zes kolommen.

Volg de onderstaande stappen om Transpose te gebruiken in VBA.

Stap 1: Definieer een subprocedure om een macro op te slaan.

Code:

 Sub Trans_Ex2 () Einde Sub

Stap 2: Bepaal het bereik waar u deze gegevens wilt transponeren. Dit kan worden gedaan met de functie Sheets.Range.Value. Ik zou de getransponeerde array opslaan in cel D1 tot I2.

Code:

 Sub Trans_Ex2 () Sheets ("Voorbeeld # 2"). Bereik ("D1: I2"). Waarde = Einde Sub

Stap 3: Gebruik WorksheetFunction.Transpose om een array A1: B6 toe te wijzen om de functie te transponeren.

Code:

 Sub Trans_Ex2 () Sheets ("Voorbeeld # 2"). Bereik ("D1: I2"). Value = WorksheetFunction.Transpose (End Sub

Stap 4: We moeten het argument voor de functie Transpose opgeven. We wilden een bereik van array A1: B6 transponeren. Gebruik dus Bereik ("A1: B6") als argument.

Code:

 Sub Trans_Ex2 () Sheets ("Voorbeeld # 2"). Bereik ("D1: I2"). Value = WorksheetFunction.Transpose (Bereik ("A1: B6")) End Sub

Stap 5: Druk op F5 of op de knop Uitvoeren om deze code uit te voeren en de uitvoer te bekijken.

Hier wordt het bereik van array A1: B6 getransponeerd en opgeslagen in een bereik van array D1: I2 met behulp van de VBA Transpose-functie in Excel.

Voorbeeld # 3 - VBA Transponeren van een array met de speciale methode Plakken

We kunnen de array ook transponeren en plakken zoals speciaal in Excel met Alt + E + S. We kunnen verschillende bewerkingen gebruiken met deze speciale methode.

Laten we dezelfde gegevens beschouwen als die we in het vorige voorbeeld hebben gebruikt.

Volg de onderstaande stappen om Transpose te gebruiken in VBA.

Stap 1: Definieer een subprocedure om de macro op te slaan.

Code:

 Sub Trans_Ex3 () Einde Sub

Stap 2: Definieer twee nieuwe variabelen, één die de gegevensbronarray (sourceRng) kan bevatten en andere die het outputarraybereik (targetRng) kunnen bevatten.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range End Sub

Houd er rekening mee dat het type van deze variabelen is gedefinieerd als (Excel.Range). Omdat we de gegevens wilden transponeren die een arraybereik zijn.

Stap 3: Stel het bronbereik in op A1: B6 (de gegevens waarvan we wilden transponeren) met de functie Sheets.Range.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Voorbeeld # 3"). Bereik ("A1: B6") End Sub

Stap 4: Stel het doel / doelbereik in op D1: I2 (het cellenbereik waar de uitvoer wordt opgeslagen) met de functie Sheets.Range.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Stel sourceRng = Sheets in ("Voorbeeld # 3"). Bereik ("A1: B6") Set targetRng = Sheets ("Voorbeeld # 3"). Bereik ( "D1: I2") Einde Sub

Stap 5: Gebruik nu de opdracht Kopiëren om het brongegevensbereik van uw werkblad te kopiëren.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Stel sourceRng = Sheets in ("Voorbeeld # 3"). Bereik ("A1: B6") Set targetRng = Sheets ("Voorbeeld # 3"). Bereik ( "D1: I2") sourceRng.Copy End Sub

Stap 6: We gaan de functie PasteSpecial op variabele targetRng gebruiken om de getransponeerde uitvoer onder het doelbereik (D1: I2) in uw werkblad op te slaan.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Stel sourceRng = Sheets in ("Voorbeeld # 3"). Bereik ("A1: B6") Set targetRng = Sheets ("Voorbeeld # 3"). Bereik ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub

Plakken : hiermee kunt u waarden in een ander formaat plakken (zoals Plakken als waarden, als formules, als formaat, enz.). Het is vergelijkbaar met dat van Excel (We doen Alt + E + S om verschillende speciale plakopties te hebben). In ons voorbeeld hebben we het ingesteld als plakken.
Bewerking : er zijn verschillende bewerkingen die kunnen worden uitgevoerd, zoals optellen, aftrekken, vermenigvuldigen, delen (op dezelfde manier als in Excel).
SkipBlanks : Indien ingesteld op True, kunt u met deze opdracht de lege plekken overslaan uit uw gegevens terwijl u speciale plakbewerkingen uitvoert. We hebben het ingesteld op False, wat betekent dat we de lege plekken niet wilden overslaan.
Transponeren : indien ingesteld op True, kunt u een reeks gegevens transponeren.

Stap 7: Voer deze code uit door op F5 of de knop Run te drukken en de uitvoer te bekijken.

Dingen om te onthouden

Wanneer u aan een eendimensionale array werkt, moet deze altijd horizontaal zijn (één rij, meerdere kolommen) om de transponeermethode toe te passen.
Het is verplicht om het aantal rijen en het aantal kolommen te achterhalen tijdens het gebruik van VBA Transpose in Excel. Als we 3 rijen en 5 kolommen hebben, zouden er na transponering 5 rijen met drie kolommen zijn.
Normaal gesproken omvat de transponeermethode geen opmaak van de brongegevens. Als u hetzelfde formaat als dat van brongegevens wilt toepassen, moet u het handmatig instellen of met behulp van de speciale optie XlPasteFormat plakken, kunt u het formaat van brongegevens aan de doelgegevens vasthouden.
Het aantal elementen dat de transponeerfunctie in een array kan opnemen, mag niet hoger zijn dan 5461.
Een array kan geen items / tekenreeksen met een lengte van meer dan 255 bevatten. Indien opgenomen, veroorzaakt dit fouten zoals 13, Type Mismatch, 5, Ongeldige procedureaanroep of argument, 1004, door de toepassing gedefinieerde of door het object gedefinieerde fout .
De bronarray kan geen null-waarde bevatten (zoals "Null", "# N / A").

Aanbevolen artikelen

Dit is een handleiding voor VBA Transpose geweest. Hier hebben we besproken hoe Excel VBA Transpose te gebruiken, samen met praktische voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen -

Handleiding voor VBA String-functie
Excel TRANSPOSE-formule
Leer VBA Case in Excel
Verwijder (verwijder) lege rijen in Excel

VBA transponeren - Hoe Excel VBA Transpose gebruiken?

Inhoudsopgave: