Macro para Transpor Valores entre Células no Excel com VBA | Excelmax Soluções e Controles

terça-feira, maio 13, 2014

Macro para Transpor Valores entre Células no Excel com VBA


TRANSPOR VALORES ENTRE CÉLULAS DO EXCEL COM VBA

transpor, célula, valores
Primeiramente quero agradecer ao meu amigo e sócio Ivair Ferrari pela oportunidade de publicar no EXCELMAX e, como estreia, vou publicar um artigo sobre uma dúvida que constantemente recebemos e que é relativamente simples, mas de grande utilidade.

Vamos a dúvida do leitor:
“Tenho uma lista em uma coluna com valores, podemos dizer de b1 até b120, e preciso transpor estes valores em colunas de doze em doze: b1 até b12 ficaria na coluna c1 até c12, já b13 até b24 ficaria na coluna d1 até d12 e assim sucessivamente...”

Trata-se de um código que tem o objetivo de transpor valores de uma determinada coluna para outras colunas, de forma sequencial e automática.
  campo, nomeado
Para tornar o código mais interessante, criei um campo onde o usuário pode escolher a quantidade de valores que cada coluna terá, já que isso depende da necessidade de cada um.

Abaixo, mostro uma planilha que simula um relatório, com valores colocados na coluna “A”, e esses valores devem ser transportados para as outras colunas na quantidade especificada no campo “Quant. de valores por coluna”, que no nosso exemplo é 7.

relatório, excel 
Além disso, coloquei um botão para o código que aciona a transposição dos valores e outro que executa a limpeza dos dados.
   
DEMONSTRAÇÃO DA MACRO  (Sub TransporValores):
                                                                 
transpor, células, macro
1) A variável “X” representa o campo nomeado “QUANT” na planilha. Nesse caso o campo nomeado foi a célula “A2” da planilha e no VBA é representada entre colchetes [QUANT].

2) A instrução CALL faz a chamada para a rotina LIMPAR, que explicarei mais adiante. Essa rotina limpa as células antes da transposição dos valores. 

3) O código (macro) inicia com a instrução DO, ou seja, determina a seleção de um range (intervalo) de células que inicia na Linha 5 da Coluna 1 e vai até a Linha 11 da mesma coluna, usando a variável “ X” - 1. Dessa forma selecionamos um range de 7 células, que é o valor de [QUANT], no nosso exemplo.

4) A instrução FOR EACH diz o que fazer com cada célula (variável CEL) no range selecionado

5) Dentro da instrução FOR EACH, colocamos uma condição IF. Ou seja, ao percorrer o range, se a célula for diferente de vazia (<> “ “), armazena o valor dessa célula na variável CEL.

6) Caso contrário, se a célula estiver vazia a instrução seguinte, EXIT DO é acionada. Essa instrução transfere o controle para a instrução seguinte ao LOOP. Ou seja, encerra o procedimento SUB TransporValores.
 
7) NEXT encerra a instrução FOR EACH, colocada mais acima.

8) LOOP encerra a instrução DO, colocada no início do código.

9) END SUB encerra toda a macro.

É interessante salientar a importância dos contadores:

Linha = Linha + 1: incrementa a variável LINHA, na qual o valor da célula é copiada, note que antes do laço FOR NEXT essa variável retorna ao valor de 5, que é sempre a linha inicial de cada coluna.

Coluna = Coluna + 1: incrementa a variável COLUNA, que indica a coluna onde o range será copiado. Esse contador está logo após o laço FOR NEXT, pois incrementa a coluna após o range ser percorrido.
 
Lin = Lin + X: Incrementa a variável LIN, indicando a linha inicial do range. Adicionado da variável “X” o contador permite iniciar o range a ser copiado sempre 7 células (no caso do nosso exemplo) depois do início do range anterior e também é colocado após o laço FOR NEXT.

DEMONSTRAÇÃO DA MACRO  (Sub Limpar):

excluir, limpar, macro

A macro LIMPAR é acionada de duas formas: no inicio da macro TransportaValores ou ao clicar no botão Limpar.


O funcionamento do código é bem simples. A variável Nlin verifica quantas linhas estão preenchidas na coluna 2 (que é a primeira coluna do relatório) e a variável Ncol verifica quantas colunas estão preenchidas na linha 5 (que é a primeira linha do relatório).

Com esses valores é possível selecionar um range (intervalo) partindo da Linha 5 da Coluna 2. Esse intervalo selecionado terminará sempre nas variáveis Nlin e Ncol. Depois disso o código dá a instrução para limpar a seleção (Selection.Clear). 

Importante verificar que há uma condição IF na macro. Ou seja, o código fará a limpeza se a célula “B5” (que é a primeira célula a ser preenchida) conter um valor (<>” “). Sem essa condição, a instrução apagaria o intervalo “A4” até “B5”.

Espero que tenham gostado da publicação! Obrigado a todos pela visita e compartilhem com seus amigos!

Link para Download: Macro para transpor valores entre células do excel

André Lucio de Moura
Administrador e Consultor

13 comentários:

  1. Excelente matéria meu amigo André! Com certeza poderá ajudar nossos amigos e leitores do Excelmax. Com certeza eu e os demais que por aqui estiverem, tiraremos grande proveito desta e de outras matérias que serão publicadas por você. Parabéns !

    ResponderExcluir
    Respostas
    1. Muito obrigado! Com certeza será um grande desafio publicar matérias à altura do Blog e contribuir para um site que tem mais de 15 milhões de visualizações!!!

      Excluir
    2. Salvo a minha vida essa planilha, estava procurando uma linha de programação para começar e desenvolver e achei já pronto a sua, obrigado por dividir com o mundo todo esse conhecimento.

      Excluir
    3. Obrigado pelo comentário! Visite sempre o blog para saber das novidades!

      Excluir
  2. Parabéns pela dedicação e pela grande matéria publicada.

    ResponderExcluir
  3. Já estava com saudades das publicações. Parabéns pela iniciativa e parabéns ao André. Excelente matéria. Tinha que ser bom para escrever aqui. Abraço prof. Ivair. Aguardo novidades.

    Solano.

    ResponderExcluir
    Respostas
    1. Obrigado, amigo Solano! Vejo que sempre participa do Blog. Seus comentários são sempre bem-vindos! Abraço!

      Excluir
  4. Olá mestre e Professor Ivair. Bom ver matérias novas por aqui.
    Parabéns ao seu amigo André pela iniciativa em participar nos ajudando, passando matérias importantes que nos ajuda muito no nosso dia-a-dia.

    Grande abraço a todos!
    Vander.

    ResponderExcluir
  5. Parabéns Prof. Ivair. Parabéns André.
    Sou fã de carteirinha por aqui

    Silvia

    ResponderExcluir
  6. macro interessante,
    A um tempo atrás eu estava querendo de algo assim, e como não achei, fiz uma que junta um monte de opções:

    direção de leitura +direção de escrita
    quantidade de linhas de leitura + quantidade de linhas de escrita até completar todas as linhas da origem.
    quantidade de colunas final + algumas outras coisas e outras que ainda estou tentando implementar, mas está difícil de casar as opções de maneira lógica

    já vai para mais de 300 linhas de macro :(

    ResponderExcluir
    Respostas
    1. Obrigado pelo comentário. Ficamos felizes por poder ajudar. Grande abraço!

      Excluir

Seja um participante desta comunidade !
Deixe aqui seu comentário e/ou sugestão.
Obrigado !

Ivair Claudio Ferrari