Transposição de VBA - Como usar o Excel VBA Transpose?

Índice:

Anonim

Transposição VBA

Sendo um usuário do Microsoft Excel, você pode ter usado frequentemente a função de transposição, que permite a troca entre linhas e colunas por uma matriz. Converter linhas em colunas e colunas em linhas é o que uma função de transposição faz por você no Excel. O número de linhas se torna um número de colunas e vice-versa. Significa que, se você tiver 2 linhas e 3 colunas em sua matriz, após a transposição, ela será alterada para uma matriz com 3 linhas e 2 colunas. Neste tutorial, passaremos pelo VBA Transpose, que permitirá automatizar o método de transposição a ser usado no Excel.

Sintaxe de Transposição no Excel VBA

O VBA Transpose possui a seguinte sintaxe:

Onde,

Arg1 : É um argumento necessário que nada mais é do que um intervalo de células que queríamos transpor (isto é, array).

A primeira parte da sintaxe nada mais é do que uma expressão sob a qual a função Transpose pode ser usada. Por exemplo, WorksheetFunction.

Como usar o Excel VBA Transpose?

Vamos aprender como usar um Transpose com poucos exemplos no Excel VBA.

Você pode baixar este modelo de VBA Transpose Excel aqui - Modelo de VBA Transpose Excel

Exemplo # 1 - Transposição VBA de matriz unidimensional

Suponha que você esteja trabalhando em dados com listas (que são uma matriz unidimensional), como nomes de funcionários ("Lalit", "Sneha", "Ethyl", "John", "Cory") e que você queira essa lista para ser colado no excel célula sábio. Vamos ver como podemos fazer isso.

Siga as etapas abaixo para usar o Transpose no VBA.

Etapa 1: insira um novo módulo e defina um novo subprocedimento para criar uma macro no VBA.

Código:

 Sub Trans_ex1 () End Sub

Etapa 2: defina uma nova variável que possa conter sua matriz unidimensional.

Código:

 Sub Trans_ex1 () Dim Arr1 como variante End Sub

Etapa 3: defina a lista como uma matriz usando a função Matriz. Essa seria a lista que você deseja colar na planilha do Excel.

Código:

 Sub Trans_ex1 () Dim Arr1 como variante Arr1 = Matriz ("Lalit", "Sneha", "Ethyl", "John", "Cory") End Sub

Etapa 4: Agora use o método Range.Value para definir o intervalo no qual esses valores você deseja colar.

Código:

 Sub Trans_ex1 () Dim Arr1 como variante Arr1 = Matriz ("Lalit", "Sneha", "Ethyl", "John", "Cory") Intervalo ("A1: A5"). Valor = Fim Sub

Etapa 5: Use Application.WorksheetFunction.Transpose na matriz fornecida para poder transpor a lista definida na variável Arr1.

Código:

 Sub Trans_ex1 () Dim Arr1 como variante Arr1 = Matriz ("Lalit", "Sneha", "Ethyl", "John", "Cory") Intervalo ("A1: A5"). Valor = Application.WorksheetFunction.Transpose (Arr1 ) End Sub

Aqui, definimos um código que permitirá ao sistema transpor os dados no array de lista chamado Arr1 e armazená-lo na célula A1: A5 na planilha ativa.

Etapa 6: pressione F5 ou o botão Executar no VBE para executar esse código e ver a saída na planilha ativa do Excel.

Você pode ver que a matriz fornecida da lista é transposta em uma única coluna e armazenada na célula A1 a A5.

Exemplo # 2 - Transposição VBA de matriz bidimensional

Suponha que você tenha uma matriz bidimensional de funcionários e seus salários como abaixo:

Este é um dado bidimensional com seis linhas e duas colunas. Após a transposição, seria uma matriz com duas linhas e seis colunas.

Siga as etapas abaixo para usar o Transpose no VBA.

Etapa 1: defina um subprocedimento para armazenar uma macro.

Código:

 Sub Trans_Ex2 () End Sub

Etapa 2: decida o intervalo em que deseja transpor esses dados. Isso pode ser feito usando a função Sheets.Range.Value. Eu estaria salvando a matriz transposta na célula D1 para I2.

Código:

 Sub Trans_Ex2 () Folhas ("Exemplo # 2"). Intervalo ("D1: I2"). Valor = Fim Sub

Etapa 3: Use WorksheetFunction.Transpose para poder atribuir uma matriz A1: B6 à função de transposição.

Código:

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

Etapa 4: precisamos especificar o argumento para a função Transpose. Queríamos transpor um intervalo da matriz A1: B6. Portanto, use Range ("A1: B6") como argumento.

Código:

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

Etapa 5: Pressione F5 ou o botão Executar para executar este código e ver a saída.

Aqui, o intervalo da matriz A1: B6 é transposto e armazenado em um intervalo da matriz D1: I2 com a ajuda da função VBA Transpose no Excel.

Exemplo # 3 - Transposição VBA de uma matriz com o Método Especial Colar

Também podemos transpor a matriz e colar tão especial quanto no Excel usando Alt + E + S. Podemos usar diferentes operações nesse método especial de colar.

Vamos considerar os mesmos dados que usamos no exemplo anterior.

Siga as etapas abaixo para usar o Transpose no VBA.

Etapa 1: defina um subprocedimento para armazenar a macro.

Código:

 Sub Trans_Ex3 () End Sub

Etapa 2: defina duas novas variáveis, uma que pode conter a matriz da fonte de dados (sourceRng) e outras que podem conter a faixa da matriz de saída (targetRng).

Código:

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

Observe que o tipo dessas variáveis ​​definidas como (Excel.Range). Porque queríamos transpor os dados, que é um intervalo de array.

Etapa 3: defina o intervalo da fonte como A1: B6 (os dados dos quais queríamos transpor) usando a função Sheets.Range.

Código:

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

Etapa 4: defina o intervalo de destino / destino como D1: I2 (o intervalo de células onde a saída será armazenada) usando a função Sheets.Range.

Código:

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

Etapa 5: Agora, use o comando Copiar para copiar o intervalo de dados de origem da planilha.

Código:

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

Etapa 6: Usaremos a função PasteSpecial na variável targetRng para salvar a saída transposta sob o intervalo de destino (D1: I2) na sua planilha.

Código:

 Sub Trans_Ex3 () Dim sourceRng como Excel.Range Dim taretRng como Excel.Range Defina sourceRng = Sheets ("Example # 3"). Range ("A1: B6") Defina targetRng = Sheets ("Example # 3"). Range ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operação: = xlNone, SkipBlanks: = False, Transpose: = True End Sub
  • Colar : permite colar valores em um formato diferente (como Colar como valores, Como fórmulas, Como formato, etc.). É semelhante ao Excel (fazemos Alt + E + S para ter diferentes opções especiais de colagem). No nosso exemplo, nós o configuramos para colar como valores.
  • Operação : Existem operações diferentes que podem ser executadas como adição, subtração, multiplicação, divisão (da mesma maneira que no Excel).
  • SkipBlanks : Se definido como True, este comando permite ignorar os espaços em branco, se houver algum, dos seus dados enquanto realiza diferentes operações especiais de colagem. Definimos como False, significa que não queremos pular os espaços em branco.
  • Transpor : Se definido como True, permite realizar a transposição de uma matriz de dados.

Etapa 7: execute esse código pressionando F5 ou o botão Executar e veja a saída.

Coisas para lembrar

  • Ao trabalhar em uma matriz unidimensional, ela sempre deve ser horizontal (uma linha, várias colunas) para aplicar o método de transposição.
  • É obrigatório descobrir o número de linhas e o número de colunas enquanto estiver usando o VBA Transpose no Excel. Se tivermos 3 linhas e 5 colunas, após a transposição, haverá 5 linhas com três colunas.
  • Normalmente, o método Transpose não inclui a formatação dos dados de origem. Se você deseja aplicar o mesmo formato que o dos dados de origem, é necessário configurá-lo manualmente ou, usando a opção especial colar XlPasteFormat, você poderá manter o formato dos dados de origem nos dados de destino.
  • O número de elementos que a função Transpose pode receber em uma matriz não pode exceder 5461.
  • Uma matriz não pode conter nenhum item / string com comprimento superior a 255. Se incluído, causará erros como 13, Tipo incompatível, 5, Chamada ou argumento de procedimento inválido, 1004, erro definido pelo aplicativo ou definido pelo objeto .
  • A matriz de origem não pode conter nenhum valor nulo (como "Nulo", "# N / A").

Artigos recomendados

Este foi um guia para o VBA Transpose. Aqui discutimos como usar o Excel VBA Transpose, juntamente com exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -

  1. Guia da função de string do VBA
  2. Fórmula TRANSPOSE do Excel
  3. Aprenda o caso VBA no Excel
  4. Remover (excluir) linhas em branco no Excel