VBA Transpose - Hur använder jag Excel VBA-transponering?

Innehållsförteckning:

Anonim

VBA Transpose

Som Microsoft Excel-användare kan du ofta ha använt transponeringsfunktion som gör det möjligt att byta mellan rader och kolumner för en matris. Att konvertera rader till kolumn och kolumner till rader är vad en transponeringsfunktion gör för dig i Excel. Antalet rader blir ett antal kolumner och vice versa. Innebär att om du har 2 rader och 3 kolumner i din matris kommer den att ändras till en matris med 3 rader och 2 kolumner. I den här tutorialen kommer vi att gå igenom VBA Transpose som gör att du kan automatisera transposmetoden du använder i Excel.

Syntax av Transpose i Excel VBA

VBA Transpose har följande syntax:

Var,

Arg1 : Det är ett obligatoriskt argument som inte är något annat än en rad celler vi ville transponera (dvs array).

Den första delen av syntaxen är inget annat än ett uttryck under vilket Transpose-funktion kan användas. Till exempel, WorksheetFunction.

Hur använder jag Excel VBA-transponering?

Vi kommer att lära dig att använda en Transpose med några exempel i Excel VBA.

Du kan ladda ner denna VBA Transpose Excel-mall här - VBA Transpose Excel Mall

Exempel 1 - VBA Transpose of One-dimensional Array

Anta att du arbetar med en data med listor (som är en endimensionell matris) som namn på en anställd som ges ("Lalit", "Sneha", "Ethyl", "John", "Cory") och du vill ha den här listan att klistras in i excelcellsmått. Låt oss se hur vi kan göra det.

Följ stegen nedan för att använda Transpose i VBA.

Steg 1: Sätt i en ny modul och definiera en ny delprocedur för att skapa ett makro i VBA.

Koda:

 Sub Trans_ex1 () Slut Sub

Steg 2: Definiera en ny variabel som kan hålla din endimensionella matris.

Koda:

 Sub Trans_ex1 () Dim Arr1 Som Variant End Sub

Steg 3: Definiera listan som en matris med Array-funktionen. Det här är listan du ville klistra in i ditt excel-ark.

Koda:

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

Steg 4: Använd nu metoden Range.Value för att definiera intervallet i vilka dessa värden du ville klistra in.

Koda:

 Sub Trans_ex1 () Dim Arr1 Som variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Värde = End Sub

Steg 5: Använd Application.WorksheetFunction.Transpose på den angivna arrayen för att kunna transponera listan definierad under variabeln Arr1.

Koda:

 Sub Trans_ex1 () Dim Arr1 Som Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Värde = Application.WorksheetFunction.Transpose (Arr1 ) Avsluta under

Här har vi definierat en kod som gör det möjligt för systemet att transponera data i listuppsättningen som heter Arr1 och sedan lagra dem under cell A1: A5 på det aktiva kalkylbladet.

Steg 6: Tryck på F5 eller Run-knappen under VBE för att köra den här koden och se utgången under aktivt Excel-kalkylblad.

Du kan se att det angivna arrayet i listan transponeras i en enda kolumn och lagras under cell A1 till A5.

Exempel 2 - VBA Transponera av tvådimensionell array

Anta att du har ett tvådimensionellt antal anställda och deras löner enligt nedan:

Detta är en tvådimensionell data med sex rader och två kolumner. Efter transponering skulle det vara en matris med två rader och sex kolumner.

Följ stegen nedan för att använda Transpose i VBA.

Steg 1: Definiera en delprocedur för att lagra ett makro.

Koda:

 Sub Trans_Ex2 () Slut Sub

Steg 2: Bestäm det område där du vill transponera dessa data. Det kan göras med Sheets.Range.Value-funktionen. Jag skulle spara den transponerade matrisen i cell D1 till I2.

Koda:

 Sub Trans_Ex2 () Sheets ("Exempel # 2"). Område ("D1: I2"). Värde = End Sub

Steg 3: Använd WorksheetFunction.Transpose för att kunna tilldela en matris A1: B6 för att transponera funktionen.

Koda:

 Sub Trans_Ex2 () Sheets ("Exempel # 2"). Område ("D1: I2"). Värde = ArbetsbladFunction.Transpose (End Sub

Steg 4: Vi måste ange argumentet för Transpose-funktionen. Vi ville transportera en rad matris A1: B6. Använd därför Range (“A1: B6”) som ett argument.

Koda:

 Sub Trans_Ex2 () Sheets ("Exempel # 2"). Område ("D1: I2"). Värde = ArbetsbladFunction.Transpose (Range ("A1: B6")) End Sub

Steg 5: Tryck på F5 eller Run-knappen för att köra den här koden och se utgången.

Här transporteras området av array A1: B6 och lagras i ett område av array D1: I2 med hjälp av VBA Transpose-funktion i Excel.

Exempel 3 - VBA Transponera en matris med Paste Special Method

Vi kan också transponera matrisen och klistra in så speciellt som i Excel med Alt + E + S. Vi kan använda olika operationer under den här specialpasta-metoden.

Låt oss överväga samma data som vi använde i föregående exempel.

Följ stegen nedan för att använda Transpose i VBA.

Steg 1: Definiera en delprocedur för att lagra makro.

Koda:

 Sub Trans_Ex3 () Slut Sub

Steg 2: Definiera två nya variabler, en som kan hålla datakällans array (sourceRng) och andra som kan hålla utmatningsområdet (targetRng).

Koda:

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

Observera att typen av dessa variabler definieras som (Excel.Range). Eftersom vi ville transportera data som är ett matrisintervall.

Steg 3: Ställ in källområdet som A1: B6 (de data vi ville ta transponering av) med hjälp av Sheets.Range-funktionen.

Koda:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Ställ in sourceRng = Sheets ("Exempel # 3"). Range ("A1: B6") Slut Sub

Steg 4: Ställ in mål- / destinationsintervallet som D1: I2 (Centret för celler där utmatningen lagras) med funktionen Sheets.Range.

Koda:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Ställ in sourceRng = Sheets ("Exempel # 3"). Range ("A1: B6") Ställ in targetRng = Sheets ("Exempel # 3"). "D1: I2") End Sub

Steg 5: Använd nu kommandot Kopiera för att kopiera källdatadelen från ditt kalkylblad.

Koda:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Ställ in sourceRng = Sheets ("Exempel # 3"). Range ("A1: B6") Ställ in targetRng = Sheets ("Exempel # 3"). "D1: I2") sourceRng.Copy End Sub

Steg 6: Vi kommer att använda funktionen PasteSpecial på variabel targetRng för att spara den transponerade utmatningen under målområdet (D1: I2) i ditt arbetsblad.

Koda:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Ställ in sourceRng = Sheets ("Exempel # 3"). Range ("A1: B6") Ställ in targetRng = Sheets ("Exempel # 3"). "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub
  • Klistra in : Gör det möjligt att klistra in värden i ett annat format (som Klistra in som värden, som formler, som format, etc.). Det liknar det med Excel (Vi gör Alt + E + S för att ha olika klistra in specialalternativ). I vårt exempel har vi ställt in den för att klistra in som värden.
  • Funktion : Det finns olika operationer som kan utföras som tillägg, subtraktion, multiplikation, delning (på samma sätt som vi kan göra i Excel).
  • SkipBlanks : Om det är satt, så låter det här kommandot hoppa över de tomma ämnen från dina data medan du gör olika klistra in specialoperationer. Vi ställde in till False, betyder att vi inte ville hoppa över tomma ämnen.
  • Transpose : Om det är satt är det möjligt att ta transponering av en matris med data.

Steg 7: Kör den här koden genom att trycka på F5 eller Run-knappen och se utgången.

Saker att komma ihåg

  • När du arbetar med en endimensionell matris bör den alltid vara horisontell (En rad, flera kolumner) för att tillämpa transposmetoden.
  • Det är obligatoriskt att ta reda på antalet rader och antalet kolumner när du använder VBA Transpose i Excel. Om vi ​​har 3 rader och 5 kolumner, efter transponering skulle det finnas 5 rader med tre kolumner.
  • Normalt inkluderar Transpose-metoden inte formatering av källdata. Om du vill tillämpa samma format som källdata måste du antingen manuellt ställa in det eller använda specialalternativet XlPasteFormat, så kan du hålla formatet källdata till måldata.
  • Antalet element Transpose-funktionen kan ta i en matris kan inte överstiga 5461.
  • En matris kan inte innehålla något objekt / sträng med längd mer än 255. Om det ingår kommer det att orsaka fel som 13, Type Mismatch, 5, Ogiltigt procedursamtal eller argument, 1004, applikationsdefinierat eller objektdefinerat fel .
  • Källarrayen kan inte innehålla något nollvärde (t.ex. "Null", "# N / A").

Rekommenderade artiklar

Detta har varit en guide till VBA Transpose. Här diskuterade vi hur du använder Excel VBA Transpose tillsammans med praktiska exempel och nedladdningsbar Excel-mall. Du kan också gå igenom våra andra föreslagna artiklar -

  1. Guide till VBA-strängfunktion
  2. Excel TRANSPOSE-formel
  3. Lär dig VBA-fall i Excel
  4. Ta bort (radera) tomma rader i Excel