VBA - PROCV Polivalente

Fevereiro 2017


Para os fãs das funções do Excel, antes de se escandalizarem, leiam as seguintes linhas.

A PROCV original é bem limitada:
  • A coluna dos dados deve estar com um offset positivo.
  • Para na primeira ocorrência encontrada e não consegue encontrar todas as ocorrências de uma coluna de busca, mesmo que não seja uma duplicata como José da Silva e João da Silva.


A PROCV polivalente proposta aqui não tem praticamente nenhum limite.
  • A lista não deve ser classificada.
  • A coluna dos dados pode ter um offset negativo.
  • O offset não tem limite, já que tem um valor de coluna válido.
  • As fórmulas podem fazer uma busca múltipla. (principal interesse)
  • Possibilidade de fazer a busca em um trecho de coluna definido.
  • A lista pode conter células vazias
  • A função pode ser utilizada a partir de outra pasta de trabalho, além da atual.
  • As células de parâmetro da função podem estar em diferentes planilhas da mesma pasta, ou em pastas de trabalho diferentes.
  • Possibilidade de incluir esta função em uma fórmula mais complexa.
  • Possibilidade de utilizar várias buscas diferentes na mesma coluna, ele não confunde as ocorrências.

Descrição


As funções devem estar em uma coluna. Cada função procura em sua própria coluna, subindo até a linha 1, uma fórmula idêntica a si mesmo (para encontrar o n° da ocorrência).

É preciso utilizar os formatos absolutos $ para as células de parâmetro

A fórmula se encontra na coluna E, linha de 3 a 9


     = PROCVMULTI ($C$3;$B$3;$H$3)


C3 = a coluna onde se encontra a lista de busca, a busca começa na linha 3. Poderia tratar-se de outra linha, então, a busca começaria no n° desta linha.
B3 = a célula critério
H3 = a coluna onde os dados serão digitados pode estar em qualquer lugar, na mesma linha.

A fórmula se encontra na coluna F, linha de 3 a 7


     = PROCVMULTI ($C$3;$B$3;$A$3; 20)


C3 = a coluna onde se encontra a lista de busca, a busca começa na linha 3. Poderia tratar-se de outra linha, então, a busca começaria no n° desta linha.
B3 = a célula critério
A3 = a coluna onde os dados serão digitados pode estar em qualquer lugar, na mesma linha. (aqui, offset negativo)
20 = a busca para na linha 20, mesmo se houver outras correspondências mais abaixo.
O que possibilita delimitar o trecho da coluna - Começo de busca na (C) 3 e fim na (C) 20
Este parâmetro é opcional: se não for informado, faz a busca em qualquer coluna informada.

O código


Cole o código abaixo em um MÓDULO PADRÃO (não em um módulo da planilha)
Option Explicit    

'RD =célula onde começa a busca    
'RC = célula critério    
'RDT = célula onde buscar o dado    
'Linha buscar até a linha... (opcional) se 0, busca até o fim da linha    

Public Function PROCVMULTI (RD As Range, RC As Range, RDT As Range, _    
            Opcional Linha As Long = 0)    

Dim i As Integer, e As Integer, Txt As String    
Dim LigE As Long, ColE As Long 'onde escrever os resultados    
Dim Col As Integer    
Dim Lig As Long, Occ As Long    
Dim PlanE As String, PlanRD As String, PlanRDT As String    

    On Error GoTo sortie    
    LinhaE = Application.Caller.Row    
    ColunaE = Application.Caller.Column    
    PlanE = Application.Caller.Parent.Name    
    Application.Volatile    
    Linha = RD.Row ' Linha onde começa a busca    
    Coluna = RD.Column 'Coluna onde começa a busca    
    PlanRD = RD.Parent.Name    
    PlanRDT = RDT.Parent.Name    
    If Ligne = 0 Then    
        Linha = Range(Sheets(PlanRD).Cells(65536, Coluna), Sheets(PlanRD).Cells(65536, Coluna)).End(xlUp).Row    
    End If    
    Busca o número de ocorrências a serem encontradas    
    For Occ = LinhaE - 1 To 1 Step -1    
        Txt = Sheets(PlanE).Cells(Occ, ColunaE).Formula    
        If Txt = Sheets(PlanE).Cells(LinhaE, ColunaE).Formula Then    
            e = e + 1    
        End If    
    Next Occ    
    For i = Linha To Linha    
        If Sheets(PlanRD).Cells(i, Coluna) = RC Then    
            If e <> 0 Then    
                e = e - 1    
            Else    
                ProcVmulti = Sheets(PlanRDT).Cells(i, RDT.Column)    
                Exit Function    
            End If    
        End If    
    Next i    
    'Se mais, encontrar correspondência    
    ProcVmulti= ""    
Exit Function    
saída:    
    'se erro na fórmula, não detectado pelo Excel.    
    ProcVmulti= "#ERRO!"    
End Function    



Tradução feita por Lucia Maurity y Nouira

Veja também

Artigo original publicado por . Tradução feita por pintuda. Última modificação: 3 de março de 2011 às 15:02 por pintuda.
Este documento, intitulado 'VBA - PROCV Polivalente', está disponível sob a licença Creative Commons. Você pode copiar e/ou modificar o conteúdo desta página com base nas condições estipuladas pela licença. Não se esqueça de creditar o CCM (br.ccm.net) ao utilizar este artigo.