Como automatizar o Vlookup usando Excel VBA

Vlookup é uma função essencial no Excel e se tornou uma parte importante do processamento de dados. Ele fornece uma série de funções que você pode combinar com um banco de dados abrangente.

Porém, se quiser automatizar esta função, você pode, principalmente se souber utilizar a linguagem de automação disponível no Excel e VBA. Veja como você pode automatizar a função vlookup no Excel VBA .

Fórmula da função Vlookup no Excel

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

Significado dos argumentos na fórmula:

• lookup_value: o valor subjacente que você deseja procurar na matriz.
• lookup_array: Dados de origem, lookup_value será usado como referência.
• column_index: Coluna retorna valor.
• exact_match/partial_match: Use 0 ou 1 para especificar o tipo de combinação.

Função VBA Vlookup no Excel

O uso da função Vlookup não é muito diferente quando você a utiliza diretamente no Excel ou via VBA. Esteja um analista de dados processando linhas de informações ou um gerente de warehouse trabalhando constantemente com novos produtos, a função vlookup é útil.

Ao trabalhar com um intervalo de pesquisa dinâmica, é melhor automatizar a fórmula, para evitar ter que manipular a mesma fórmula várias vezes no Excel. Ao automatizar a função Vlookup no VBA, você pode realizar cálculos de várias colunas com um clique.

Ilustração

Este conjunto de dados tem duas partes: uma tabela de pesquisa e pontos de dados de destino. A tabela de consulta contém 3 colunas, com dados nos dois campos Subcategoria e Nome do Produto:

A tabela de destino possui as colunas apropriadas, sem subpastas ou dados de nome de produto. Observe que os valores da coluna ID do pedido também aparecem na mesma coluna da tabela de consulta:

1. Faça as configurações necessárias

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

Sub vlookup_fn1()End Sub

A subrotina é um contêiner para código VBA e é necessária para executá-la com êxito. Outra alternativa é criar um formulário de usuário VBA para tornar a IU mais interativa.

2. Declarar variáveis ​​e criar escopos de referência

Primeiro, você precisa declarar os tipos de dados das variáveis ​​usando o comando Dim:

Dim i as integer, lastrow as long

A seguir, crie uma variável lastrow , para armazenar o valor da última linha preenchida no intervalo de pesquisa. A função lastrow usa a função end(xldown) para calcular a referência da última linha em um intervalo específico.

Neste exemplo, a variável lastrow contém o valor da última linha preenchida no intervalo de pesquisa, especificamente 17.

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

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

Porém, antes de entrar no loop, você deve armazenar os valores do intervalo em uma nova variável ( my_range ) da seguinte forma:

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

Você deve especificar explicitamente a referência de célula inicial (F3) e a referência de coluna final (H). O VBA adiciona automaticamente valores de 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 loop é necessário quando você lida com componentes dinâmicos. Na tabela de destino, a linha 2 é a primeira linha de dados. Altere esse valor se os 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 é flexível; O loop será executado até que a condição seja falsa. Use a condição para testar 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)

Esses comandos definem os valores das células da linha atual, respectivamente nas colunas 2 e 3. 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 definido inicialmente e o índice da coluna relevante para buscar o valor final.

O código vlookup está pronto, mas você ainda precisa incrementar a variável de linha a cada vez no loop:

i = i + 1

Cada vez que o loop é executado, ele incrementa o valor i em 1 . Lembre-se, o valor da linha inicial é 2, o incremento ocorre cada vez que o loop é executado. Use a palavra-chave loop para definir o final deste bloco de código.

Quando todo o código for executado, ele preencherá ambas as colunas com resultados, com base nos valores da tabela de origem.

Abaixo está todo o código de 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

Você pode criar um botão em uma tabela do Excel para executá-lo com um clique. Insira a forma desejada na 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 sub-rotina e clique em OK .

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

Acima está como usar o Excel VBA para automatizar funções de pesquisa . Espero que o artigo seja útil para você.