VBA Transpose - Hvordan bruke Excel VBA-transponering?

Innholdsfortegnelse:

Anonim

VBA Transpose

Som Microsoft Excel-bruker kan du ofte ha brukt transponeringsfunksjon som gjør det mulig å bytte mellom rader og kolonner for en matrise. Å konvertere rader i kolonne og kolonner til rader er hva en transponeringsfunksjon gjør for deg i Excel. Antall rader blir et antall kolonner og omvendt. Betyr at hvis du har to rader og 3 kolonner i matrisen, vil den etter transponering skifte til en matrise med 3 rader og 2 kolonner. I denne opplæringen vil vi gå gjennom VBA Transpose som lar deg automatisere transponeringsmetoden du bruker i Excel.

Syntaks for transponering i Excel VBA

VBA Transpose har følgende syntaks:

Hvor,

Arg1 : Det er et nødvendig argument som ikke er annet enn et utvalg av celler vi ønsket å transponere (dvs. matrise).

Den første delen av syntaksen er ikke annet enn et uttrykk som Transpose-funksjon kan brukes under. For eksempel WorksheetFunction.

Hvordan bruke Excel VBA-transponering?

Vi lærer hvordan du bruker Transpose med få eksempler i Excel VBA.

Du kan laste ned denne VBA Transpose Excel Template her - VBA Transpose Excel Template

Eksempel 1 - VBA Transpose of One-dimensional Array

Anta at du jobber med en data med lister (som er en endimensjonal matrise) som navn på en ansatt som er gitt ("Lalit", "Sneha", "Ethyl", "John", "Cory") og du vil ha denne listen som skal limes inn excel cellemessig. La oss se hvordan vi kan gjøre dette.

Følg trinnene nedenfor for å bruke Transpose i VBA.

Trinn 1: Sett inn en ny modul og definer en ny delprosedyre for å lage en makro i VBA.

Kode:

 Sub Trans_ex1 () Slutt Sub

Trinn 2: Definer en ny variabel som kan inneholde endimensjonal matrise.

Kode:

 Sub Trans_ex1 () Dim Arr1 Som Variant End Sub

Trinn 3: Definer listen som en matrise ved å bruke Array-funksjonen. Dette ville være listen du ønsket å lime inn i excelarket.

Kode:

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

Trinn 4: Bruk nå metoden Range.Value for å definere området hvor disse verdiene du ønsket å lime inn.

Kode:

 Sub Trans_ex1 () Dim Arr1 Som Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Verdi = End Sub

Trinn 5: Bruk Application.WorksheetFunction.Transpose på den gitte arrayen for å kunne transponere listen som er definert under Arr1-variabelen.

Kode:

 Sub Trans_ex1 () Dim Arr1 Som Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Verdi = Application.WorksheetFunction.Transpose (Arr1 ) Slutt sub

Her har vi definert en kode som lar systemet transponere dataene i listearray kalt Arr1 og deretter lagre dem under celle A1: A5 på det aktive regnearket.

Trinn 6: Trykk på F5 eller Run-knappen under VBE for å kjøre denne koden og se utdataene under aktivt Excel-regneark.

Du kan se at det gitte arrayet av listen blir transponert til en enkelt kolonne og lagret under celle A1 til A5.

Eksempel 2 - VBA Transpose of two-dimensional Array

Anta at du har et todimensjonalt utvalg av ansatte og deres lønn som nedenfor:

Dette er en todimensjonal data med seks rader og to kolonner. Etter transponering ville det være en matrise med to rader og seks kolonner.

Følg trinnene nedenfor for å bruke Transpose i VBA.

Trinn 1: Definer en delprosedyre for å lagre en makro.

Kode:

 Sub Trans_Ex2 () Slutt Sub

Trinn 2: Velg området hvor du vil transponere disse dataene. Det kan gjøres ved å bruke Sheets.Range.Value-funksjonen. Jeg ville lagre det transponerte arrayet i celle D1 til I2.

Kode:

 Sub Trans_Ex2 () Sheets ("Eksempel nr. 2"). Område ("D1: I2"). Verdi = End Sub

Trinn 3: Bruk WorksheetFunction.Transpose for å kunne tilordne en matrix A1: B6 for å transponere funksjon.

Kode:

 Sub Trans_Ex2 () Sheets ("Eksempel # 2"). Område ("D1: I2"). Verdi = WorksheetFunction.Transpose (End Sub

Trinn 4: Vi må spesifisere argumentet for Transpose-funksjon. Vi ønsket å transponere et utvalg av matrix A1: B6. Bruk derfor Range (“A1: B6”) som et argument.

Kode:

 Sub Trans_Ex2 () Sheets ("Eksempel # 2"). Område ("D1: I2"). Verdi = WorksheetFunction.Transpose (Range ("A1: B6")) End Sub

Trinn 5: Trykk på F5 eller Run-knappen for å kjøre denne koden og se utdataene.

Her blir utvalg av array A1: B6 transponert og lagret i et område av matrise D1: I2 ved hjelp av VBA Transpose-funksjon i Excel.

Eksempel nr. 3 - VBA Transponere en gruppe med Paste Special Method

Vi kan også transponere matrisen og lime inn så spesiell som i Excel ved å bruke Alt + E + S. Vi kan bruke forskjellige operasjoner under denne spesielle limmetoden.

La oss vurdere de samme dataene som vi har brukt i forrige eksempel.

Følg trinnene nedenfor for å bruke Transpose i VBA.

Trinn 1: Definer en delprosedyre for å lagre makroen.

Kode:

 Sub Trans_Ex3 () Slutt Sub

Trinn 2: Definer to nye variabler, en som kan inneholde datakildearrayen (sourceRng) og andre som kan inneholde output array-området (targetRng).

Kode:

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

Vær oppmerksom på at typen av disse variablene er definert som (Excel.Range). Fordi vi ønsket å transponere dataene som er et utvalg.

Trinn 3: Angi kilden for A1: B6 (dataene vi ønsket å ta transponering av) ved hjelp av Sheets.Range-funksjonen.

Kode:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Sett sourceRng = Sheets ("Eksempel # 3"). Område ("A1: B6") Slutt Sub

Trinn 4: Angi mål- / destinasjonsområdet som D1: I2 (Utvalget av celler der utdataene skal lagres) ved å bruke Sheets.Range-funksjonen.

Kode:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Sett kildeRng = Ark ("Eksempel # 3"). Område ("A1: B6") Sett targetRng = Ark ("Eksempel # 3"). Område ( "D1: I2") Slutt sub

Trinn 5: Nå bruker du kommandoen Kopier for å kopiere kildedatafeltet fra arbeidsarket.

Kode:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Sett kildeRng = Ark ("Eksempel # 3"). Område ("A1: B6") Sett targetRng = Ark ("Eksempel # 3"). Område ( "D1: I2") sourceRng.Copy End Sub

Trinn 6: Vi kommer til å bruke funksjonen PasteSpecial på variabel targetRng for å lagre den transponerte utdata under målområdet (D1: I2) i arbeidsarket.

Kode:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Sett kildeRng = Ark ("Eksempel # 3"). Område ("A1: B6") Sett targetRng = Ark ("Eksempel # 3"). Område ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub
  • Lim inn : Gjør det mulig å lime inn verdier i et annet format (som Lim inn som verdier, som formler, som format, etc.). Det ligner på Excel (Vi gjør Alt + E + S for å ha forskjellige limspesialalternativer). I vårt eksempel har vi satt det til å lime inn som verdier.
  • Operasjon : Det er forskjellige operasjoner som kan utføres som addisjon, subtraksjon, multiplikasjon, divisjon (på samme måte som vi kan gjøre i Excel).
  • SkipBlanks : Hvis satt er True, lar denne kommandoen hoppe over de tomme emnene fra dataene dine mens du utfører spesielle lim spesielle operasjoner. Vi satte den til False, betyr at vi ikke ønsket å hoppe over blanke.
  • Transpose : Hvis satt er True, tillater det å transponere en rekke data.

Trinn 7: Kjør denne koden ved å trykke på F5 eller Run-knappen og se utdataene.

Ting å huske

  • Når du arbeider med en endimensjonal matrise, bør den alltid være vannrett (En rad, flere kolonner) for å bruke transponeringsmetoden.
  • Det er obligatorisk å finne ut antall rader og antall kolonner mens du bruker VBA Transpose i Excel. Hvis vi har 3 rader og 5 kolonner, ville det etter transponering være 5 rader med tre kolonner.
  • Normalt inkluderer Transpose-metoden ikke formatering av kildedataene. Hvis du vil bruke det samme formatet som kildedata, må du manuelt stille det inn eller ved å lime inn spesialalternativ XlPasteFormat vil du kunne holde formatet på kildedataene i måldataene.
  • Antall elementer Transpose-funksjonen kan ta i en matrise kan ikke overstige 5461.
  • En matrise kan ikke inneholde noe element / streng som har lengde over 255. Hvis inkludert, vil det forårsake feil som 13, Type Mismatch, 5, Ugyldig prosedyresamtale eller argument, 1004, applikasjonsdefinert eller objektdefinert feil .
  • Kildearrangementet kan ikke inneholde noen nullverdi (for eksempel “Null”, “# N / A”).

Anbefalte artikler

Dette har vært en guide til VBA Transpose. Her diskuterte vi hvordan du bruker Excel VBA Transpose sammen med praktiske eksempler og nedlastbar Excel-mal. Du kan også gå gjennom andre foreslåtte artikler -

  1. Veiledning for VBA-strengfunksjon
  2. Excel TRANSPOSE Formula
  3. Lær VBA-sak i Excel
  4. Fjern (Slett) tomme rader i Excel