Dicas de VBA – vLookupValues() uma Variante do Vlookup()

Por Jorge Paulino para o Pplware!

A função VLOOKUP (PROCV em Português) é uma função muito interessante e muito utilizada pois permite procurar por um valor numa tabela/matriz e devolver o valor que está na coluna ou na coluna à direita, indicando-se para isso o índice. Existe também a função HLOOKUP (PROCH em Português) para pesquisas na horizontal.

Para quem nunca utilizou, este é o sintaxe da função:

=VLOOKUP(valor procurar; tabela; coluna a devolver; resultado aproximado)

Mas vejamos um exemplo simples para mostrar como funciona a função VLOOKUP.

Como podem ver na imagem anterior temos uma tabela com duas colunas. Na coluna A temos a selecção, utilizando um ‘x’ (podíamos procurar por número, ID, etc.) e na coluna B temos nomes de B2 a B9. Se quisermos procurar o nome que está seleccionado podíamos usar a seguinte função:

=VLOOKUP("x";A2:B9;2;FALSE)

O resultado na célula onde colocássemos a função seria “Paulo”

Breve descrição dos argumentos:

• O primeiro argumento (x) indica o valor a procurar;
• O segundo argumento (A2:B9) indica a matriz, sendo a coluna da esquerda a coluna onde vamos procurar os valores (neste caso a coluna A);
• O terceiro argumento (2) indica a coluna de onde será devolvido o valor. Neste caso vamos devolver o valor que está na coluna B (2ª coluna), e por isso indicamos o valor 2, mas se tivéssemos mais colunas, por exemplo números de telefone na coluna C, poderíamos indicar como valor 3 e modificar o segundo argumento de A2:B9 para A2:C9.
• O quarto argumento (FALSE) indica que não queremos resultados aproximados mas sim exactos

Mas por muito interessante e útil que a função seja só nos devolve um resultado, ou seja, se seleccionar dois ou três nomes, apenas irá devolver o primeiro que encontrar.

Usando VBA podemos resolver esta “limitação” e devolver múltiplos resultados de acordo com a selecção. Para isso podemos criar um novo módulo e utilizar o seguinte código:

Public Function VLookupValues(lookupValue As String, _

            lookupRange As Range, _

            columnIndex As Integer, _

            Optional distinct As Boolean = False) _

            As String

Dim x As Long

Dim result As String

On Error GoTo errVLookupValues

    ' Ciclo em todas as linhas do range

    For x = 1 To lookupRange.Rows.Count

        Dim r As Range

        Set r = lookupRange.Cells(x, 1)

        ' Verifica se o valor é igual ao indicado

        If r.Value = lookupValue Then

            Dim str As String

            ' Utiliza o Offset para ir buscar a coluna indicada

            str = r.Offset(, columnIndex - 1).Value & ";"

            ' Caso se pretenda apenas os valores diferentes e

            ' já  esteja já na lista não faz a concatenação

            If distinct And InStr(1, result, str, vbTextCompare) = 0 _

                        Or Not distinct Then

                result = result & str

            End If

        End If

    Next

    ' Se não encontrar nada coloca uma mensagem genérica

    If Len(result) > 0 Then

        ' Remove o último ";"

        VLookupValues = Left(result, Len(result) - 1)

    Else

        VLookupValues = "Não Encontrado"

    End If

Exit Function

errVLookupValues:

    VLookupValues = ""

End Function

Para tornar a função mais versátil foi adicionado um argumento opcional que permite indicar se queremos resultados únicos ou não. O sintaxe para utilizar esta função é o seguinte:

=vLookupValues(valor procurar; tabela; coluna a devolver; resultados únicos)

E utilizando a mesma tabela agora com várias selecções:

Se utilizarmos a função =VLookupValues("x";A2:B9;2;FALSE), em qualquer célula, obtemos o resultado: Rui;Ana;Paulo;Paula

Podemos ainda indicar no último argumento se queremos ou não resultado únicos.

 

Escrito por: Jorge Paulino Homepage: Página Autor: Jorge Paulino
Download de Exemplo aqui