Aprenda a automatizar seus Vlookups com Excel VBA

Leve sua programação do Excel um passo adiante usando a popular função VLOOKUP em seu código VBA.

Vlookup é uma função essencial do Excel e se tornou uma parte vital do processamento de dados. Ele fornece algumas das funcionalidades que você normalmente associa a um banco de dados completo.

Mas e se você quiser automatizar essa função? Sim, isso é possível, principalmente se você souber usar a linguagem de automação nativa do Excel, o VBA. Veja como você pode automatizar a função vlookup no Excel VBA.

A sintaxe do Vlookup

A sintaxe da função vlookup é bastante simples, mas possui quatro aspectos nos quais você deve focar, mesmo ao automatizá-la no Excel VBA.

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Os argumentos da função têm o seguinte significado:

• lookup_value: este é o valor principal que você deseja pesquisar na matriz de pesquisa.
• lookup_array: estes são os dados de origem, que o lookup_value usará como referência.
• column_index: Coluna da qual retornar o valor.
• exact_match/partial_match: Use 0 ou 1 para determinar o tipo de correspondência.

Preparação de dados

Este conjunto de dados tem duas partes: uma tabela de pesquisa e os pontos de dados de destino.

A tabela de pesquisa consiste em três colunas, com dados preenchendo os campos Subcategoria e Nome do produto:

A tabela de destino possui colunas correspondentes, sem dados de subcategoria ou nome de produto. Observe que os valores na coluna ID do pedido também estão presentes na mesma coluna da tabela de pesquisa:

Usando o Vlookup diretamente em uma planilha do Excel

Para preencher as colunas de destino, B e C, você pode usar a função vlookup do Excel.

Na célula B2, insira a seguinte fórmula:

=VLOOKUP($A2, $F$3:$H$17, 2, 0)

Da mesma forma, na célula C2, insira esta fórmula:

=VLOOKUP($A2, $F$3:$H$17, 3, 0)

Você pode arrastar a fórmula da célula B2 até o final da coluna, B17. Repita esse processo também para a coluna C. Os valores de cada entrada subsequente serão atualizados automaticamente em ambas as colunas.

Os valores que você passa para a função vlookup são:

• A2: O valor de pesquisa está nesta célula.
• F3:H17: Consiste no intervalo de pesquisa.
• 2/3: as colunas de referência das quais obter o valor.
• 0: indica uma correspondência exata.

Função Vlookup do Excel VBA

A função vlookup tem muitas semelhanças, quer você a use diretamente no Excel ou via VBA. Quer você seja um analista de dados que lida com linhas de dados ou um gerente de estoque que lida regularmente com novos produtos, você pode se beneficiar usando o vlookup.

Ao trabalhar com um intervalo de pesquisa dinâmico, é sempre melhor automatizar suas fórmulas, para evitar a aplicação repetida de fórmulas no Excel. Ao automatizar sua função Vlookup no VBA, você pode realizar cálculos de várias colunas com um único clique.

1. Execute a configuração necessária

Comece abrindo o editor de codificação (pressione Alt + F11 ou navegue até a guia Desenvolvedor) em uma nova pasta de trabalho do Excel e adicione um módulo para começar a escrever seu código. No editor de código, adicione as seguintes linhas na parte superior da janela de codificação para criar uma sub-rotina:

 Sub vlookup_fn1()End Sub

A subrotina é um contêiner para seu código VBA e é fundamental para executá-lo com êxito. Uma alternativa é criar um formulário de usuário VBA para tornar sua IU mais interativa.

2. Declare suas variáveis e crie seus intervalos de referência

Primeiro, você precisa declarar os tipos de dados variáveis usando a instrução Dim:

 Dim i as integer, lastrow as long

Em seguida, crie uma variável lastrow para armazenar o valor da última linha preenchida em seu intervalo de pesquisa. A função lastrow usa a função end(xldown) para calcular a referência da linha final dentro do intervalo especificado.

Nesse caso, a variável lastrow armazena o último valor de linha preenchido do intervalo de pesquisa,17.

 lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

No exemplo acima, a tabela de destino estende-se de A2:C17, enquanto a tabela de origem estende-se de F2:H17. A fórmula vlookup percorrerá cada valor armazenado na coluna A, procurará na tabela de origem e colará as entradas correspondentes nas colunas B e C.

No entanto, antes de entrar no loop, você deve armazenar os valores do intervalo em uma nova variável (my_range), como segue:

 my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

Você deve definir explicitamente a referência de célula inicial (F3) e a referência de coluna final (H). O VBA anexa automaticamente o valor da linha para completar a matriz de pesquisa.

3. Escreva um loop para percorrer cada valor de pesquisa

Antes de escrever o loop, defina o valor da linha inicial, 2. Definir um valor inicial para o seu loop é essencial porque você está lidando com elementos dinâmicos. Na tabela de destino, a linha 2 é a primeira linha dos dados. Altere esse valor se seus dados começarem em uma linha diferente.

 i = 2

Você pode usar um loop do-while para processar cada linha, começando na linha 2 e terminando na linha 17. Assim como o restante do código, esse aspecto é dinâmico; o loop será executado até que a condição seja falsa. Use a condição para verificar um valor não vazio na primeira célula da linha atual.

 Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

Dentro do loop, você pode chamar a função VLookup para preencher as células:

 Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Essas instruções definem o valor das células na linha atual, nas colunas 2 e 3, respectivamente. Eles usam o objeto WorksheetFunction para chamar a função VLookup, passando o valor correspondente da coluna 1 para pesquisar. Eles também passam o intervalo que você definiu anteriormente e o índice da coluna relevante da qual obter o valor final.

O código vlookup está pronto, mas você ainda precisa aumentar a variável de linha a cada vez que executa o loop:

 i = i + 1

Cada vez que o loop é executado, o valor de i aumenta em 1. Lembre-se, o valor da linha inicial é 2 e o incremento acontece toda vez que o loop é executado. Use a palavra-chave loop para definir o final do bloco de código do loop.

Ao executar o código inteiro, ele preencherá os resultados em ambas as colunas, com base nos valores da tabela de origem.

Aqui está o código completo para referência:

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Crie um botão para executar o código

Com o código resolvido, você pode criar um botão em sua pasta de trabalho do Excel para executá-lo com um único clique. Insira sua forma preferida em uma planilha Excel em branco, clique com o botão direito sobre ela e clique na opção Atribuir Macro.

Na caixa de diálogo, selecione o nome da sua sub-rotina e clique em OK.

Quando você quiser executar seu código, clique no botão para ver como os dados são preenchidos imediatamente.

Usando Excel VBA para automatizar funções de pesquisa

Excel VBA é uma linguagem flexível, bem ajustada para facilitar a automação nas diferentes facetas do MS Excel. Existem muitas opções no MS Excel VBA, desde a automatização de funções do Excel até a criação automática de tabelas dinâmicas elaboradas.

Ao trabalhar com diferentes segmentos do Excel, você pode experimentar diversas opções para tornar sua vida mais fácil e eficiente.