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

TRANSPOR VALORES ENTRE CÉLULAS DO EXCEL COM VBA

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.

 

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.

 

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):

                                                                 

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):

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