Pesquisa Personalizada Simples 2 – A Missão

Estimados leitores amantes do excel…
Devido a inúmeros comentários e pedidos acerca da ferramenta que disponibilizei no artigo sobre pesquisa simples, hoje, estou incrementando algumas melhorias de acordo com as sugestões enviadas para mim.
Ajustei algumas linhas de comando que faziam com que as funções fossem estáticas, do tipo, a pesquisa só seria realizada na Plan1, ou, também não era possível escolher em qual coluna pesquisar. Seria necessário, reconfigurar toda a estrutura dos códigos programados. Hoje, portanto, reprogramei esta função da pesquisa personalizada, para, por exemplo:

  • Pesquisar em mais de uma planilha;
  • Pesquisar definindo a coluna onde os dados serão procurados (desde que, as planilhas possuam a mesma estrutura de colunas);
  • Exibir em que planilha o resultado foi encontrado.

Algumas outras sugestões dos leitores, eu disponibilizarei, em um próximo artigo. Mas, já adiantando o assunto, será incluir nessa pesquisa a possibilidade de atualizar esses dados na base de dados da planilha onde eles estão escritos. Não deixem de acompanhar esta “saga” (risos). Bem… mas vamos a missão de hoje.
Para quem não leu o artigo anterior sobre Criando uma Pesquisa Personalizada Simples, sugiro que dê uma lida.
Não vou me estender em muitos detalhes na explicação de como trabalha o código, até porque, ele está bem documentado. Na verdade, é mais uma atualização do código anterior com adição de uma caixa de listagem contendo os campos da planilha para escolha no momento da pesquisa. No caso do usuário selecionar “Tudo”, a busca será realizada em toda a planilha base.
Supondo que minhas planilhas que contêm dados para serem pesquisados sejam:

  • Plan1
  • PlanBase2

E, supondo que os campos de minhas planilhas são:
Nome – Estado – Função – Status
Teremos:
Uma função para definir as planilhas que serão bases para as pesquisas.

Function ConfigPlanilhasBase() As Variant
Dim sNomeDasPlanilhas As String
    'Digite o nome das Planilhas onde os dados deverão ser procurados,
    'separados por ponto-e-vírgula (;)
    '
    sNomeDasPlanilhas = "Plan1;PlanBase2"     '<----- Informe as planilhas aqui
    Do While (Right(sNomeDasPlanilhas, 1) = ";")
        sNomeDasPlanilhas = Left(sNomeDasPlanilhas, Len(sNomeDasPlanilhas) - 1)
    Loop
    ConfigPlanilhasBase = Split(sNomeDasPlanilhas, ";")
End Function

Uma rotina para configurar a Lista de Campos das planilhas. Nessa rotina, deve ser digitado o nome dos campos, geralmente os títulos dos cabeçalhos dos dados escritos nas tabelas de dados.

Sub ConfigurarListaDeCampos()
    With ComboBox1
        .Style = fmStyleDropDownList
        .AddItem "Tudo"   '<--- Esta é utilizada para definir quando pesquisar em toda a planilha
        .AddItem "Nome"
        .AddItem "Estado"
        .AddItem "Função"
        .AddItem "Status"
        .ListIndex = 0
    End With
End Sub

Já na função abaixo, seu efeito é cruzar o nome dos campos com a sua posição na planilha. Ou seja, ao selecionar o campo “Nome” por exemplo, a minha busca será feita na coluna “A”. Noutro caso, se for por “Função”, a pesquisa será feita na coluna “C”.

Function ConfigColunas(ByVal sNomeCampo As String) As String
    Select Case sNomeCampo
        Case "Nome"
            ConfigColunas = "A"
        Case "Estado"
            ConfigColunas = "B"
        Case "Função"
            ConfigColunas = "C"
        Case "Status"
            ConfigColunas = "D"
        Case Else        '<--- Esta é utilizada para definir quando pesquisar em toda a planilha
            ConfigColunas = ""
    End Select
End Function

O funcionamento é bem simples e sua configuração uma vez realizada, não necessitará ser alterada. A escolha dos campos será feita na tela do formulário de pesquisa, e serão exibidos os resultados encontrados em todas as planilhas definidas no código.
Abaixo segue o código completo, já com as funções acima e as atualizações das demais rotinas.

Public MatrizResultadosLinha As Variant
Public MatrizResultadosPlanilha As Variant
Public Total_Ocorrencias As Long
Private Sub btn_Procurar_Click()
    If txt_Procurar.Text = "" Then
        MsgBox "Digite um valor para a pesquisa"
    Else
        Call ProcuraPersonalizada(Me.txt_Procurar.Text, ComboBox1.Text)
    End If
End Sub
Private Sub SpinButton1_Change()
Dim sLinha As Long
Dim iPlanilha As Integer
Dim TotalOcorrencias As Long
    TotalOcorrencias = SpinButton1.Max + 1
    sLinha = MatrizResultadosLinha(SpinButton1.Value)
    iPlanilha = MatrizResultadosPlanilha(SpinButton1.Value)
    Label_Registros_Contador.Caption = SpinButton1.Value + 1 & " de " & TotalOcorrencias
    With Sheets(iPlanilha)
        Label_PlanBase.Caption = "Em " & .Name
        TextBox1.Text = .Cells(sLinha, 1).Value
        TextBox2.Text = .Cells(sLinha, 2).Value
        TextBox3.Text = .Cells(sLinha, 3).Value
        TextBox4.Text = .Cells(sLinha, 4).Value
    End With
End Sub
Private Sub ProcuraPersonalizada(ByVal TermoPesquisado As String, ByVal sPesquisarNoCampo As String)
Dim Busca As Range
Dim Primeira_Ocorrencia As String
Dim ResultadosLinha As String
Dim ResultadosPlanilha As String
Dim sSearchInCol As String
Dim arrPesquisarNasPlanilhas As Variant
Dim i As Integer
    'Define a Coluna onde a informação será pesquisada
    sSearchInCol = ConfigColunas(sPesquisarNoCampo)
    'Define as Planilhas onde a informação será pesquisada
    arrPesquisarNasPlanilhas = ConfigPlanilhasBase
    'Inicializa os resultados
    ResultadosLinha = ""
    ResultadosPlanilha = ""
    'Executa a busca
    For i = 0 To UBound(arrPesquisarNasPlanilhas)
        With Sheets(arrPesquisarNasPlanilhas(i))
            If sSearchInCol = "" Then
                Set Busca = .Cells.Find(What:=TermoPesquisado, After:=.Range("A1"), LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
            Else
                Set Busca = .Range(sSearchInCol & ":" & sSearchInCol).Find( _
                    What:=TermoPesquisado, _
                    After:=.Range(sSearchInCol & "1"), _
                    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
            End If
            'Caso tenha encontrado alguma ocorrência...
            If Not Busca Is Nothing Then
                Primeira_Ocorrencia = Busca.Address
                ResultadosLinha = ResultadosLinha & IIf((Len(ResultadosLinha) > 0), ";", "") & Busca.Row 'Lista o primeiro resultado na variavel - linha da ocorrência
                ResultadosPlanilha = ResultadosPlanilha & IIf((Len(ResultadosPlanilha) > 0), ";", "") & .Index 'Lista o primeiro resultado na variavel - planilha da ocorrência
                'Neste loop, pesquisa todas as próximas ocorrências para
                'o termo pesquisado
                Do
                    If sSearchInCol = "" Then
                        Set Busca = .Cells.FindNext(After:=Busca)
                    Else
                        Set Busca = .Range(sSearchInCol & ":" & sSearchInCol).FindNext(After:=Busca)
                    End If
                    'Condicional para não listar o primeiro resultado
                    'pois já foi listado acima
                    If Not Busca.Address Like Primeira_Ocorrencia Then
                        ResultadosLinha = ResultadosLinha & ";" & Busca.Row
                        ResultadosPlanilha = ResultadosPlanilha & ";" & .Index
                    End If
                Loop Until Busca.Address Like Primeira_Ocorrencia
            End If
        End With
    Next i
    If Len(ResultadosLinha) > 0 Then    'Se foram encontrados resultados
        MatrizResultadosLinha = Split(ResultadosLinha, ";")
        MatrizResultadosPlanilha = Split(ResultadosPlanilha, ";")
        'Atualiza dados iniciais no formulário
        SpinButton1.Max = UBound(MatrizResultadosLinha)  'Valor maximo do seletor de registros
        'habilita o seletor de registro
        SpinButton1.Enabled = True
        'indicador do seletor de registros
        Label_Registros_Contador.Caption = "1 de " & UBound(MatrizResultadosLinha) + 1
        'Box com o conteudo encontrado
        With Sheets(CInt(MatrizResultadosPlanilha(0)))
            Label_PlanBase.Caption = "Em " & .Name
            TextBox1.Text = .Cells(MatrizResultadosLinha(0), 1).Value
            TextBox2.Text = .Cells(MatrizResultadosLinha(0), 2).Value
            TextBox3.Text = .Cells(MatrizResultadosLinha(0), 3).Value
            TextBox4.Text = .Cells(MatrizResultadosLinha(0), 4).Value
        End With
    Else    'Caso nada tenha sido encontrado, exibe mensagem informativa
        SpinButton1.Enabled = False     'desabilita o seletor de registros
        Label_Registros_Contador.Caption = ""   'zera os resultados encontrados
        Label_PlanBase.Caption = ""
        'limpa os campos do formulário
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        MsgBox "Nenhum resultado para '" & TermoPesquisado & "' foi encontrado."
    End If
End Sub
Private Sub UserForm_Initialize()
    SpinButton1.Enabled = False
    Label_Registros_Contador.Caption = ""
    Call ConfigurarListaDeCampos
End Sub
Sub ConfigurarListaDeCampos()
    With ComboBox1
        .Style = fmStyleDropDownList
        .AddItem "Tudo"     '<--- Esta é utilizada para definir quando pesquisar em toda a planilha
        .AddItem "Nome"
        .AddItem "Estado"
        .AddItem "Função"
        .AddItem "Status"
        .ListIndex = 0
    End With
End Sub
Function ConfigColunas(ByVal sNomeCampo As String) As String
    Select Case sNomeCampo
        Case "Nome"
            ConfigColunas = "A"
        Case "Estado"
            ConfigColunas = "B"
        Case "Função"
            ConfigColunas = "C"
        Case "Status"
            ConfigColunas = "D"
        Case Else           '<--- Esta é utilizada para definir quando pesquisar em toda a planilha
            ConfigColunas = ""
    End Select
End Function
Function ConfigPlanilhasBase() As Variant
Dim sNomeDasPlanilhas As String
    'Digite o nome das Planilhasonde os dados deverão ser procurados,
    'separados por ponto-e-vírgula (;)
    '
    sNomeDasPlanilhas = "Plan1;PlanBase2"   '<----- Informe as planilhas aqui
    Do While (Right(sNomeDasPlanilhas, 1) = ";")
        sNomeDasPlanilhas = Left(sNomeDasPlanilhas, Len(sNomeDasPlanilhas) - 1)
    Loop
    ConfigPlanilhasBase = Split(sNomeDasPlanilhas, ";")
End Function

Amigos, este código é apenas um exemplo e pode ser adaptado por vocês a medida de suas necessidades. Fiquem à vontade para comentá-lo aqui no site.
E não deixem de ler a parte 3, onde ensino como editar os dados pesquisados através do próprio formulário, salvando na planilha base de dados, ok.
Até a próxima.

Download dos Arquivos

Você também pode gostar de ler esses artigos

13 comentários em “Pesquisa Personalizada Simples 2 – A Missão”

  2. Bom dia
    Reinaldo
    Na hora de pesquisar da segiuinte mensagem:
    ERRO DE TEMPO NA EXECUCÃO ‘448’
    ARGUMENTO NOMEADO NÃO LOCALIZADO
    Tem comome ajudar o que ocorre???

    Responder

  3. No VB o seguinte comando fica com selecionado com o fundo amarelo dessa forma:
    Set Busca = .Cells.Find(What:=TermoPesquisado, After:=.Range(“A1”), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    Por favor pode me ajudar?

    Responder

  5. Prezados,
    Estou fazendo uma planilha para uma amiga controlar os membros da igreja dela. Basicamente tenho uma aba com uma estrutura de cadastro e com uma macro eses dados são transportados para uma base. Agora criei uma aba usanfp PROCV para pesquisar os dados de determinado membro. Agora o que preciso é após efetuar alterações nestes dados sobrepor estas informações na base. Vocês podem me ensinar como fazer isto? Qual o código da MACRO que pesquisa na base e faz a substituição?
    Aguardo retorno,
    Shirley

    Responder
  6. Pingback: Pesquisa Personalizada Simples 3 – Parte Final : Excel do Seu Jeito
  7. Pingback: Criando uma Pesquisa Personalizada Simples : Excel do Seu Jeito
  8. Pingback: Excel – Perguntas e Respostas #1 : Excel do Seu Jeito

Deixe um comentário