Vlookup é uma função de planilha no Excel, mas também pode ser usada no VBA, a funcionalidade do Vlookup é semelhante à funcionalidade no VBA e na planilha, já que é uma função de planilha, o método para usar o Vlookup no VBA é através de Application.WorksheetFunction método e os argumentos permanecem os mesmos.
Função VLOOKUP no Excel VBA
A função VLOOKUP no Excel é usada para pesquisar um valor em uma matriz e retornar seu valor correspondente de outra coluna. O valor a ser pesquisado deve estar presente na primeira coluna. Também é necessário mencionar se deve procurar uma correspondência exata ou uma correspondência aproximada. A função de planilha VLOOKUP pode ser usada na codificação VBA. A função não é VBA embutida e, portanto, só pode ser chamada usando a planilha.
A função VLOOKUP no Excel tem a seguinte sintaxe:
Em que, lookup_value é o valor a procurar, table_arrray é a tabela, col_index_num é o número da coluna do valor de retorno, range_lookup significa se a correspondência é exata ou aproximada. range_lookup pode ser TRUE / FALSE ou 0/1.
No código VBA, a função VLOOKUP pode ser usada como:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Como usar o VLookup no Excel VBA?
Abaixo estão alguns exemplos do Código VLookup no Excel VBA.
Código VLookup no Excel VBA Exemplo # 1
Vamos ver como podemos chamar a função de planilha VLOOKUP no Excel VBA.
Suponha que você tenha dados sobre a carteira de alunos, nome e as notas médias obtidas por eles.
Agora, você deseja consultar as notas obtidas por um aluno com ID 11004.
Para pesquisar o valor, siga as seguintes etapas:
- Vá para a guia Desenvolvedor e clique em Visual Basic.
- Na janela do VBA, vá para Inserir e clique em Módulo.
- Agora, escreva o código VBA VLOOKUP. O seguinte código VBA VLOOKUP pode ser usado.
Sub vlookup1 ()
Dim student_id As Long
Dim marks As Long
student_id = 11004
Set myrange = Range (“B4: D8”)
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
Em primeiro lugar, defina um ID de aluno, que é o valor a ser pesquisado. Portanto, nós definimos,
student_id = 11004
A seguir, definimos o intervalo em que o valor e o valor de retorno existem. Uma vez que nossos dados estão presentes nas células B4: D8, definimos um intervalo-mirra como:
Definir myrange = Range (“B4: D8”)
Finalmente, inserimos a função VLOOKUP usando a função de planilha em uma variável, marcada como:
marcas = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Para imprimir as marcas em uma caixa de mensagem, vamos usar o seguinte comando:
MsgBox “Student with ID:” & student_id & ”obtido” & marcas & ”marcas”
Ele retornará:
Um aluno com ID: 11004 obteve 85 notas.
Agora, clique no botão Executar.
Você notará que uma caixa de mensagem aparecerá na planilha do Excel.
Código VLookup no Excel VBA Exemplo # 2
Suponha que você tenha os dados sobre os nomes dos funcionários e seus salários. Esses dados são fornecidos pelas colunas B e C. Agora, você precisa escrever um código VBA VLOOKUP de forma que dado o nome do funcionário em uma célula, F4, o salário do funcionário seja retornado na célula G4.
Vamos escrever o código VBA VLOOKUP.
- Defina o intervalo em que os valores estão presentes, ou seja, colunas B e C.
Definir myrange = Range (“B: C”)
- Defina o nome do funcionário e insira o nome da célula F4.
Definir nome = intervalo (“F4”)
- Defina salário como célula G4.
Definir salário = Faixa (“G4”)
- Agora, chame a função VLOOKUP usando WorksheetFunction em VBA e insira-a em salary.Value. Isso retornará o valor (saída da função Vlookup) na célula G4. A seguinte sintaxe pode ser usada:
salary.Value = Application.WorksheetFunction.VLookup (name, myrange, 2, False)
- Agora, execute o módulo. A célula G4 conterá o salário do funcionário após a execução do código VBA VLOOKUP.
Suponha que você altere o valor da célula F4 para “David” na planilha e execute novamente o código, que retornará o salário de David.
Código VLookup no Excel VBA Exemplo # 3
Suponha que você tenha os dados do funcionário de sua empresa, contendo sua carteira de identidade, nomes, Departamento e salário. Usando o Vlookup no VBA, você deseja obter os detalhes do salário de um funcionário usando seu nome e departamento.
Como a função vlookup no Excel busca o lookup_value em apenas uma única coluna, que é a primeira coluna da table_array, é necessário que você primeiro faça uma coluna contendo o “Nome” e “Departamento” de cada funcionário.
No VBA, vamos inserir os valores “Nome” e “Departamento” na coluna B da planilha.
Para fazer isso, vá para a guia Desenvolvedor e clique em Visual Basic. Em seguida, vá para Inserir e clique em Módulo para iniciar um novo módulo.
Vamos agora escrever o código, de forma que a coluna B contenha os valores da coluna D (nome) e da coluna E.
A sintaxe é fornecida como:
Em primeiro lugar, use um loop 'for' de i = 4, uma vez que os valores estão começando na 4ª linha neste caso. O loop continuará até o final da última linha da coluna C. Portanto, a variável I pode ser usada como um número de linha dentro do loop 'for'.
Em seguida, insira o valor a ser atribuído à Célula (número_da_linha, coluna B), que pode ser dado como Células (i, “B”). Valor, como Célula (número_da_linha, coluna D) & “_” & Célula (número_da_linha, coluna E )
Suponha que você queira atribuir a célula B5 = D5 & “_” & E5, você pode simplesmente usar o código como:
Células (5, “B”). Valor = Células (5, “D”). Valor & “_” e Células (5, “E”). Valor
Agora, vamos procurar o valor de pesquisa na matriz B5: E24. Você precisa primeiro inserir o valor de pesquisa. Vamos pegar o valor (Nome e Departamento) do usuário. Para fazer isso,
- defina três variáveis, nome, departamento e valor_procurado como uma string.
- Pegue a entrada de nome do usuário. Use o código:
name = InputBox (“Digite o nome do funcionário”)
O conteúdo da caixa de entrada “Digite o…” será exibido na caixa de prompt quando você executar o código. A string inserida no prompt será atribuída à variável de nome.
- Tire o departamento do usuário. O pode ser feito da mesma forma que acima.
departamento = InputBox (“Digite o departamento do funcionário”)
- Atribua o nome e o departamento com “_” como separador para a variável lookup_val usando a seguinte sintaxe:
lookup_val = nome & “_” & departamento
- Escreva a sintaxe vlookup para pesquisar o valor_procurado no intervalo B5: E24 retorna em um salário variável.
Inicialize o salário variável:
Dim salary enquanto
Use a função Vlookup para encontrar lookup_val. Table_array pode ser dada como Gama ( “B: F”), e o salário está presente na 5 th coluna. A seguinte sintaxe pode ser usada:
salary = Application.WorksheetFunction.VLookup (lookup_val, Range (“B: F”), 5, False)
- Para imprimir o salário em uma caixa de mensagem, use a sintaxe:
MsgBox (O salário do funcionário é ”e salário)
Agora, execute o código. Uma caixa de prompt aparecerá na planilha na qual você pode inserir o nome. Depois de inserir o nome (diga Sashi) e clique em OK.
Irá abrir outra caixa na qual você pode entrar no departamento. Depois de entrar no departamento, diga TI.
Vai imprimir o salário do funcionário.
Se o Vlookup puder localizar qualquer funcionário com o nome e departamento, ele apresentará um erro. Suponha que você dê o nome “Vishnu” e o departamento “TI”, ele retornará o erro em tempo de execução '1004'.
Para resolver esse problema, você pode especificar no código, que neste tipo de erro, imprima “Valor não encontrado”. Para fazer isso,
- Antes de usar a sintaxe vlookup, use o seguinte código-
On Error GoTo Message
Verifica:
O código final (de Check :) será monitorado e, se receber um erro, irá para a instrução “mensagem”
- No final do código (Before End Sub), especifique que, se o número do erro for 1004, imprima na caixa de mensagem "Dados do funcionário ausentes". Isso pode ser feito usando a sintaxe:
Mensagem:
If Err.Number = 1004 Then
MsgBox (“Dados do funcionário ausentes”)
Fim se
Módulo 1:
Sub vlookup3 ()For i = 4 To Cells (Rows.Count, “C”). End (xlUp) .Row
Cells (i, “B”). Value = Cells (i, “D”). Value & “_ ”& Células (i,“ E ”). Valor
Próximo nome iDim As String
Dim departamento As String
Dim lookup_val As String
Dim salary As Longname = InputBox (“ Digite o nome do funcionário ”)
department = InputBox (“ Digite o departamento de o funcionário ”)
lookup_val = name &“ _ ”& departmentOn Error GoTo Message
check:
salary = Application.WorksheetFunction.VLookup (lookup_val, Range (“ B: F ”), 5, False)
MsgBox (“ O salário do funcionário é ”E salário) Mensagem:
If Err.Number = 1004 Then
MsgBox (“ Dados do funcionário ausentes ”)
End IfEnd Sub
Coisas para lembrar sobre VLookup no Excel VBA
- A função Vlookup pode ser chamada no Excel VBA usando WorksheetFunction.
- A sintaxe da função vlookup permanece a mesma no Excel VBA.
- Quando o código VBA vlookup não consegue encontrar o valor_procurado, ele fornecerá um erro 1004.
- O erro na função vlookup pode ser gerenciado usando uma instrução goto se retornar um erro.
