VBA Transpose - Comment utiliser Excel VBA Transpose?

Table des matières:

Anonim

VBA Transpose

En tant qu'utilisateur Microsoft Excel, vous avez souvent utilisé la fonction de transposition qui permet l'échange entre les lignes et les colonnes pour un tableau. Convertir des lignes en colonnes et des colonnes en lignes est ce qu'une fonction de transposition fait pour vous dans Excel. Le nombre de lignes devient un nombre de colonnes et vice versa. Signifie, si vous avez 2 lignes et 3 colonnes dans votre tableau, après la transposition, il se transformera en un tableau avec 3 lignes et 2 colonnes. Dans ce tutoriel, nous allons passer par VBA Transpose qui vous permettra d'automatiser la méthode de transposition que vous utiliserez dans Excel.

Syntaxe de transposition dans Excel VBA

VBA Transpose a la syntaxe suivante:

Où,

Arg1 : C'est un argument requis qui n'est rien d'autre qu'une plage de cellules que nous voulions transposer (c'est-à-dire un tableau).

La première partie de la syntaxe n'est rien d'autre qu'une expression sous laquelle la fonction Transpose peut être utilisée. Par exemple, WorksheetFunction.

Comment utiliser Excel VBA Transpose?

Nous allons apprendre à utiliser une transposition avec quelques exemples dans Excel VBA.

Vous pouvez télécharger ce modèle VBA Transpose Excel ici - VBA Transpose Excel Template

Exemple # 1 - Transposition VBA d'un tableau unidimensionnel

Supposons que vous travaillez sur une donnée avec des listes (qui est un tableau unidimensionnel) comme les noms d'un employé donnés ("Lalit", "Sneha", "Ethyl", "John", "Cory") et vous voulez cette liste à coller dans Excel CellSage. Voyons comment nous pouvons y arriver.

Suivez les étapes ci-dessous pour utiliser Transpose dans VBA.

Étape 1: insérez un nouveau module et définissez une nouvelle sous-procédure pour créer une macro dans VBA.

Code:

 Sub Trans_ex1 () End Sub

Étape 2: Définissez une nouvelle variable pouvant contenir votre tableau unidimensionnel.

Code:

 Sub Trans_ex1 () Dim Arr1 As Variant End Sub

Étape 3: définir la liste en tant que tableau à l'aide de la fonction Array. Ce serait la liste que vous vouliez coller dans votre feuille Excel.

Code:

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

Étape 4: utilisez maintenant la méthode Range.Value pour définir la plage dans laquelle vous souhaitez coller ces valeurs.

Code:

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

Étape 5: Utilisez Application.WorksheetFunction.Transpose sur le tableau donné pour pouvoir transposer la liste définie sous la variable Arr1.

Code:

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

Ici, nous avons défini un code qui permettra au système de transposer les données dans un tableau de liste appelé Arr1, puis de les stocker sous la cellule A1: A5 sur la feuille de calcul active.

Étape 6: Appuyez sur F5 ou sur le bouton Exécuter sous VBE pour exécuter ce code et voir la sortie sous la feuille de calcul Excel active.

Vous pouvez voir que le tableau donné de la liste est transposé dans une seule colonne et stocké sous la cellule A1 à A5.

Exemple # 2 - Transposition VBA d'un tableau bidimensionnel

Supposons que vous ayez un ensemble d'employés en deux dimensions et leurs salaires comme ci-dessous:

Il s'agit d'une donnée à deux dimensions avec six lignes et deux colonnes. Après la transposition, ce serait un tableau à deux lignes et six colonnes.

Suivez les étapes ci-dessous pour utiliser Transpose dans VBA.

Étape 1: définir une sous-procédure pour stocker une macro.

Code:

 Sub Trans_Ex2 () End Sub

Étape 2: Décidez de la plage dans laquelle vous souhaitez transposer ces données. Cela peut être fait en utilisant la fonction Sheets.Range.Value. Je sauverais le tableau transposé dans la cellule D1 à I2.

Code:

 Sub Trans_Ex2 () Sheets ("Example # 2"). Range ("D1: I2"). Value = End Sub

Étape 3: utilisez WorksheetFunction.Transpose pour pouvoir affecter un tableau A1: B6 à la fonction de transposition.

Code:

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

Étape 4: Nous devons spécifier l'argument de la fonction Transpose. Nous voulions transposer une gamme de tableau A1: B6. Ainsi, utilisez Range («A1: B6») comme argument.

Code:

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

Étape 5: Appuyez sur F5 ou sur le bouton Exécuter pour exécuter ce code et voir la sortie.

Ici, la plage du tableau A1: B6 est transposée et stockée dans une plage du tableau D1: I2 à l'aide de la fonction VBA Transpose dans Excel.

Exemple # 3 - Transposition VBA d'un tableau avec la méthode spéciale Coller

Nous pouvons également transposer le tableau et le coller comme spécial, tout comme dans Excel en utilisant Alt + E + S. Nous pouvons utiliser différentes opérations sous cette méthode spéciale de collage.

Considérons les mêmes données que celles que nous avons utilisées dans l'exemple précédent.

Suivez les étapes ci-dessous pour utiliser Transpose dans VBA.

Étape 1: définissez une sous-procédure pour stocker la macro.

Code:

 Sub Trans_Ex3 () End Sub

Étape 2: Définissez deux nouvelles variables, une qui peut contenir le tableau de sources de données (sourceRng) et d'autres qui peuvent contenir la plage de tableaux de sortie (targetRng).

Code:

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

Veuillez noter que le type de ces variables défini comme (Excel.Range). Parce que nous voulions transposer les données qui sont une plage de tableaux.

Étape 3: définissez la plage source comme A1: B6 (les données dont nous voulions prendre la transposition) en utilisant la fonction Sheets.Range.

Code:

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

Étape 4: définissez la plage cible / destination sur D1: I2 (la plage de cellules où la sortie sera stockée) à l'aide de la fonction Sheets.Range.

Code:

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

Étape 5: Maintenant, utilisez la commande Copier pour copier la plage de données source de votre feuille de calcul.

Code:

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

Étape 6: Nous allons utiliser la fonction PasteSpecial sur la variable targetRng pour enregistrer la sortie transposée sous la plage cible (D1: I2) dans votre feuille de calcul.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Example # 3"). Range ("A1: B6") Set targetRng = Sheets ("Example # 3"). Range ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub
  • Coller : permet de coller des valeurs dans un format différent (comme Coller comme valeurs, comme formules, comme format, etc.). Il est similaire à celui d'Excel (nous faisons Alt + E + S pour avoir différentes options spéciales de collage). Dans notre exemple, nous l'avons défini pour coller en tant que valeurs.
  • Opération : Il existe différentes opérations qui peuvent être effectuées comme l'addition, la soustraction, la multiplication, la division (de la même manière que nous pouvons le faire dans Excel).
  • SkipBlanks : si elle est définie sur True, cette commande permet d'ignorer les blancs, le cas échéant, de vos données lors de différentes opérations spéciales de collage. Nous l'avons défini sur False, ce qui signifie que nous ne voulions pas sauter les blancs.
  • Transposer : Si défini sur True, il permet de prendre la transposition d'un tableau de données.

Étape 7: Exécutez ce code en appuyant sur F5 ou sur le bouton Exécuter et voyez la sortie.

Choses dont il faut se rappeler

  • Lorsque vous travaillez sur un tableau unidimensionnel, il doit toujours être horizontal (une ligne, plusieurs colonnes) afin d'appliquer la méthode de transposition.
  • Il est obligatoire de connaître le nombre de lignes et le nombre de colonnes lors de l'utilisation de VBA Transpose dans Excel. Si nous avons 3 lignes et 5 colonnes, après transposition, il y aurait 5 lignes avec trois colonnes.
  • Normalement, la méthode Transpose n'inclut pas le formatage des données source. Si vous souhaitez appliquer le même format que celui des données source, vous devez soit le définir manuellement, soit utiliser l'option spéciale coller XlPasteFormat, vous pourrez conserver le format des données source dans les données cibles.
  • Le nombre d'éléments que la fonction Transpose peut prendre dans un tableau ne peut pas dépasser 5461.
  • Un tableau ne peut contenir aucun élément / chaîne ayant une longueur supérieure à 255. S'il est inclus, il provoquera des erreurs telles que 13, incompatibilité de type, 5, appel ou argument de procédure non valide, 1004, erreur définie par l'application ou définie par l'objet .
  • Le tableau source ne peut contenir aucune valeur nulle (comme «Null», «# N / A»).

Articles recommandés

Cela a été un guide pour VBA Transpose. Ici, nous avons discuté de la façon d'utiliser Excel VBA Transpose avec des exemples pratiques et un modèle Excel téléchargeable. Vous pouvez également consulter nos autres articles suggérés -

  1. Guide de la fonction de chaîne VBA
  2. Formule Excel TRANSPOSE
  3. Apprenez le cas VBA dans Excel
  4. Supprimer (supprimer) des lignes vides dans Excel