Excel substituir índice corresp por vba

[Renato Aboim]

Boa tarde pessoal, tudo bem?

 

Estou com um problema que não consigo resolver sozinho, imagino que vocês possa me dar um apoio:

 

Estou montando uma planilha para controle de horas dos funcionários. Na aba de lançamento de horas, criei uma tabela com hora de início, fim, etc. Nessa tabela tenho 2 listas suspensas para organizar as tarefas com alguns critérios. Essas listas suspensas são correlacionadas Exemplo:

• Se na 1ª escolho "A", na segunda vai aparecer "10, 20, 30..."
• Se na 1ª escolho "B", na segunda vai aparecer "coração, estrela, triângulo,..."

Essa 1ª aba está ok!

 

Tenho uma 2ª aba, onde lançaremos as atividades. Nessa tabela lançaremos os itens aleatoriamente, porém no seu preenchimento teremos um TAG, cuja função é separar os itens de forma a criar as listas suspensas (o TAG seria "A", "B", etc, conforme exemplo acima). Enquanto utilizo colunas auxiliares e o conjunto de fórmulas ÍNDICE, CORRESP, funciona belezinha!

 

Temos o exemplo em anexo.

 

Problema: essa solução que bolei faz o arquivo ficar muito GRANDE e PESADO. Na Internet comenta-se que fórmulas que utilizam matrizes fazem o arquivo ficar grande e lento.

Acontece que o exemplo é uma planilha extremamente resumida. A condição das listas suspensas, e suas colunas auxiliares, se repetem umas 58 vezes. Já testei aqui com o lançamento de dados (que podem ultrapassar as centenas de linhas facilmente) e o arquivo chegar a travar o computador, quando arrasto a tabela de tarefas. Até a lista suspensa fica lenta!

 

Dúvida: Se o problema está nas fórmulas ÍNDICE e CORRESP (que utilizam matrizes), tem como substitui-las por VBA? Só um detalhe: a tabela de tarefas pode crescer de forma livre (aumentando o número de linhas), então penso numa condição: se alguma célula da nova linha estiver preenchida, o VBA "cola" a fórmula na posição desejada.

 

Eu sei que o ideal é criar um banco de dados no access, mas não tenho a manha.

 

Obrigado e um abraço

Renato

 

DUVIDA.xlsx

[Edson Luiz Branco]

Não entendi, @Renato Aboim

- Procure explicar detalhadamente mas relacionando com os endereços de intervalo/nomes/campos de tabela/planilha existentes em seu arquivo e nos dizendo qual o resultado final desejado e onde.

- Verifique os intervalos nomeados que porventura possuam links com arquivos em seu PC, pois diversas delas estão com erro #REF! (possivelmente vínculo quebrado), como por ex. DEMANDAS, EL.PE, etc.

 

[Renato Aboim]

Oi @Edson Luiz Branco!

Vou tentar explicar melhor. Arquivo em anexo exemplifica o funcionamento.

 

Aba TAREFAS

Ela será utilizada para colocar todas as tarefas a serem desenvolvidas.

Existem equipes diferentes que utilizarão esta lista

• No exemplo coloquei EQUIPE.1 à EQUIPE.3 na coluna EQUIPE (coluna C)
• No exemplo eu coloquei ATIVIDADE 1 à ATIVIDADE 10 na coluna ATIVIDADE (coluna D)
• As colunas E,F,G se referem à organização da lista de atividades referente à EQUIPE.1
• As colunas H,I,J se referem à organização da lista de atividades referente à EQUIPE.2
• As colunas K,L,M se referem à organização da lista de atividades referente à EQUIPE.3

Nessas colunas auxiliares, perceba que:

• Utilizo a 1ª para separar as linhas que contém a informação de qual equipe se refere
• Utilizo a 2ª para jogar as linhas em branco para baixo, agrupando o conteúdo nas linhas superiores
• Utilizo a 3ª para buscar o campo ATIVIDADE. Essa 3ª coluna (de cada EQUIPE) será utilizada para criar a lista suspensa na aba HORAS.

Aba HORAS

Aqui todo mundo irá lançar as horas trabalhadas (todos os funcionários de todas as equipes).

Porém, para facilitar a busca pelas atividades relacionadas à EQUIPE à qual o funcionário pertence, criei uma condição dupla através de listas suspensas.

• Na coluna EQUIPE eu escolho qual equipe eu pertenço
• Na coluna ATIVIDADE eu escolho a atividade relacionada à equipe à qual eu pertenço.

O QUE FUNCIONA:

A aba HORAS funciona perfeitamente.

A aba TAREFAS funciona perfeitamente, com a lógica que descrevi e que está no arquivo em anexo.

 

QUAL O PROBLEMA:

Observe que sempre utilizei tabelas, isso tem uma justificativa: tanto a aba HORAS quanto a aba TAREFAS podem crescer indefinidamente, sem restrições. Ao utilizar tabelas, quando o funcionário estender a tabela (para baixo), as fórmulas, condições, etc., já são copiadas automaticamente.

 

O problema é que ao fazer o teste pra valer (com centenas de linhas), o arquivo ficou extremamente grande e pesado. Isso ocorreu porque as fórmulas utilizadas nas colunas auxiliares da aba TAREFAS utilizam matrizes, o que requer muita memória do computador.

 

QUAL A SOLUÇÃO QUE BUSCO:

Eu imagino (não tenho certeza) que, seu usarmos VBA para criar a solução de fórmulas das colunas auxiliares na aba TAREFAS, o arquivo não fique não lento e pesado.

 

Detalhes:

• O Tchan! da lista de tarefas é ser ilimitada. Ou seja, a cada preenchimento de uma linha (nos campos EQUIPE e ATIVIDADE, que são os campos que os funcionários terão acesso), as fórmulas nas colunas auxiliares apareçam.
• Essa planilha em anexo é um exemplo simplificado do problema. A planilha real possui o equivalente à 40 equipes (podendo aumentar).
• O termo equipe é uma simplificação da planilha para melhor entendimento (por isso que a verdadeira possui "40").

FIM

Certo de sua atenção, agradeço.

DUVIDA R01.xlsx