Criando uma Pesquisa Personalizada Simples
Oi pessoal. Hoje vamos criar uma pesquisa personalizada bem simples com base no comando Localizar do excel. Nessa pesquisa, os dados retornados serão exibidos num formulário.
Vamos começar então.
Tendo em mãos uma planilha simples:
| Nome | Estado | Função | Status |
| Marcelle Silva | Rio de Janeiro | Técnica | Ativo |
| Cláudia Mara | Goiás | Técnica | Ativo |
| Ricardo Teles | Minas Gerais | Supervisor | Ativo |
| James Wood | Rio de Janeiro | Técnico | Férias |
| Aline Aguiar Moraes | Bahia | Técnica | Ativo |
| Cláudia Moraes Lima | Rio Grande do Sul | Gerente Executiva | Ativo |
| Marcelo de Souza | Rio de Janeiro | Supervisor | Aposentado |
| Maria Antonieta | Bahia | Secretária | Férias |
| Jackie Wistern | Bahia | Técnico | Ativo |
Temos, assim, quatro colunas: nome, estado, função e status. Adicione um botão de comando nessa planilha, definindo seu caption como “Localizar na Planilha”. Dê um duplo clique nesse controle para abrir o evento Click no ambiente VBA e podermos editá-lo. Quando abrir, digite isso:
Private Sub CommandButton1_Click() frmBusca.Show False 'Exibe o Formulário da Pesquisa End Sub |
Bom, agora, adicione um formulário (Menu Inserir > UserForm) e dê o nome “frmBusca”.
Adicione a ele 5 TextBox, 8 Label, 1 CommandButton e 1 SpinButton. O formulário deve ficar parecido com este:
Defina os seguintes nomes aos controles:
CommandButton = btn_Procurar
SpinButton = SpinButton1
Label que vai mostrar a posição do registro atual = Label_Registros_Contador (Os demais Labels não precisa alterar os nomes)
Textbox onde vai digitar o termo da pesquisa = txt_Procurar (Os demais textbox deixe como TextBox1, TextBox2, TextBox3 e TextBox4)
Após ajustar o design do formulário, tecle F7 para exibir o seu código e declare as duas variáveis públicas abaixo:
Public MatrizResultados As Variant Public Total_Ocorrencias As Long |
Em seguida, crie a sub-rotina que executará a pesquisa no formulário:
Private Sub ProcuraPersonalizada(ByVal TermoPesquisado As String) Dim Busca As Range Dim Primeira_Ocorrencia As String Dim Resultados As String 'Executa a busca Set Busca = Plan1.Cells.Find(What:=TermoPesquisado, After:=Range("A1"), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) 'Caso tenha encontrado alguma ocorrência... If Not Busca Is Nothing Then Primeira_Ocorrencia = Busca.Address Resultados = Busca.Row 'Lista o primeiro resultado na variavel 'Neste loop, pesquisa todas as próximas ocorrências para 'o termo pesquisado Do Set Busca = Plan1.Cells.FindNext(After:=Busca) 'Condicional para não listar o primeiro resultado 'pois já foi listado acima If Not Busca.Address Like Primeira_Ocorrencia Then Resultados = Resultados & ";" & Busca.Row End If Loop Until Busca.Address Like Primeira_Ocorrencia MatrizResultados = Split(Resultados, ";") 'Atualiza dados iniciais no formulário SpinButton1.Max = UBound(MatrizResultados) '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(MatrizResultados) + 1 'Box com o conteudo encontrado TextBox1.Text = Plan1.Cells(MatrizResultados(0), 1).Value TextBox2.Text = Plan1.Cells(MatrizResultados(0), 2).Value TextBox3.Text = Plan1.Cells(MatrizResultados(0), 3).Value TextBox4.Text = Plan1.Cells(MatrizResultados(0), 4).Value 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 '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 |
Feito isso, falta criar os procedimentos a serem executados ao abrir o formulário, ao clicar no botão pesquisar e ao clicar nos botões que percorrem os resultados obtidos. Segue abaixo esse códigos nesta ordem:
Private Sub UserForm_Initialize() SpinButton1.Enabled = False Label_Registros_Contador.Caption = "" End Sub Private Sub btn_Procurar_Click() If Me.txt_Procurar.Text = "" Then MsgBox "Digite um valor para a pesquisa" Else Call ProcuraPersonalizada(Me.txt_Procurar.Text) End If End Sub Private Sub SpinButton1_Change() Dim Linha As Long Dim TotalOcorrencias As Long TotalOcorrencias = SpinButton1.Max + 1 Linha = MatrizResultados(SpinButton1.Value) Label_Registros_Contador.Caption = SpinButton1.Value + 1 & " de " & TotalOcorrencias TextBox1.Text = Plan1.Cells(Linha, 1).Value TextBox2.Text = Plan1.Cells(Linha, 2).Value TextBox3.Text = Plan1.Cells(Linha, 3).Value TextBox4.Text = Plan1.Cells(Linha, 4).Value End Sub |
Pronto. Após todos os códigos digitados, basta acessar a planilha, clicar no botão “Localizar na Planilha”, digitar o texto que deseja encontrar e será exibido nos campos do formulário todas as ocorrências obtidas na consulta.
Um abraço a todos.
Termos aprendidos neste artigo:
| Find | Localiza informações específicas em um intervalo, e retorna um objeto Range representando a primeira célula onde essas informações se encontram. Retorna Nothing se nenhuma coincidência for encontrada. Não afeta a seleção da célula ativa.
expressão.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) expressão necessária. Uma expressão que retorna um objeto Range. What Variant necessário. Os dados a serem procurados. Pode ser uma seqüência de caracteres ou qualquer tipo de dados do Microsoft Excel. After Variant opcional. A célula depois da qual você deseja que a pesquisa comece. Corresponde à posição da célula ativa quando uma pesquisa é feita a partir da interface do usuário. Observe que After precisa ser uma única célula no intervalo. Lembre-se de que a pesquisa começa depois dessa célula; a célula especificada não é pesquisada até que o método dê a volta e chegue a ela. Se você não especificar esse argumento, a pesquisa começará após a célula do canto superior esquerdo do intervalo. LookIn Variant opcional. O tipo de informação. LookAt Variant opcional. Pode ser uma das seguintes constantes XlLookAt: xlWhole ou xlPart. SearchOrder Variant opcional. Pode ser uma das seguintes constantes XlSearchOrder: xlByRows ou xlByColumns. SearchDirection XlSearchDirection opcional. A direção da pesquisa.
MatchCase Variant opcional. True para fazer a pesquisa fazer distinção entre maiúsculas e minúsculas. O valor padrão é False. MatchByte Variant opcional. Usado somente se você tiver selecionado ou instalado suporte de linguagem de byte duplo. True para que os caracteres de dois bytes coincidam somente com caracteres de dois bytes. False para que os caracteres de dois bytes coincidam com seus equivalentes de um byte. SearchFormat Variant opcional. O formato da pesquisa |
||||
| FindNext | Continua uma pesquisa que tenha começado com o método Find. Localiza a próxima célula que coincida com as mesmas condições e retorna um objeto Range representando essa célula. Não afeta a seleção da célula ativa.
expressão.FindNext(After) Método FindNextexpressão necessária. Uma expressão que retorna um objeto Range. After Variant opcional. A célula depois da qual você deseja procurar. Corresponde à posição da célula ativa quando uma pesquisa é feita a partir da interface do usuário. Observe que After precisa ser uma única célula no intervalo. Lembre-se de que a pesquisa começa depois desta célula; a célula especificada não é pesquisada até que o método dê a volta e chegue à ela. Se esse argumento não for especificado, a pesquisa começará após a célula do canto superior esquerdo do intervalo. |
||||
| Show | Exibe um objeto UserForm.
Método Show Sintaxe [object.]Show modal A sintaxe do método Show tem as seguintes partes:
|
38 Comentários
Muito obrigado, amigo. Você me tirou muitas dúvidas com esse artigo!
Reply
Comentário feito em outubro 23rd, 2008 às 11:15
Esta expressão abaixo só fica no vermelho, ja conferi..reconferi…e não encontrei onde esta o erro…se ela não pretejar..rssss não funciona…ONDE FOI QUE EU ERREI ???
Set Busca=Plan1.Cells.Find(What:=TermoPesquisado,After:=Range(“A1″), LookIn:=xlFormulas,_
LookAt:=xlPart,SearchOrder:=xlByRows,SearchDirection:=xlNext,_
MatchCase:=False, SearchFormat:=False)
Reply
exceldoseujeito Reply:
novembro 23rd, 2008 at 14:49
Reinaldo, acredito que você tenha copiado o código neste artigo e colado no excel. Alguns caracteres quando são copiados do html tem uma codificação que não é interpretada corretamente no ambiente VBA, inclusive.
Faça o seguinte, neste seu caso específico, redigite as “aspas duplas” no código. Então vai dar certo.
Obrigado pela visita.
Reply
Comentário feito em novembro 23rd, 2008 às 11:08
Qdo eu mando executar ele chega a abrir a caixa de pesquisa, mas quando vou pesquisar ele gera o erro 424 dizendo q o objeto eh obrigatório, como eu conseguiria contornar esse erro?
Reply
exceldoseujeito Reply:
novembro 24th, 2008 at 18:53
Davidson, seu provável erro deve ser na digitação do código. Após o erro, o análise de erros do VB deve marcar em amarelo a linha com problema. Veja o que está escrito e poste aqui novamente. Abraço.
Reply
Comentário feito em novembro 24th, 2008 às 17:07
Private Sub CommandButton1_Click()
frmBusca.Show False
End Sub
A linha fica no frmBusca.show
Reply
Comentário feito em novembro 25th, 2008 às 10:55
Você tem alguma apostila sobre VBA, eu tentei redigitar os códigos, mas não foi. Mesmo assim vlw pela ajuda ab!
Reply
Comentário feito em novembro 25th, 2008 às 16:40
Boa tarde!
Você sabe dizer se no excel há alguma maneira de verifiar a a existencia de algum caminho por exemplo. Eu gostaria de fazer um If com a seguinte sentença
If o endereço abaixo exista, execute-o
Windows(“Processo de devolução de leasing.xls”).Activate
Else
execute esse caminho alternativo
c:\….
Reply
Comentário feito em novembro 27th, 2008 às 16:39
Este site me foi muito util, parabens ao seu idealizador,
grato!
wenes
Reply
exceldoseujeito Reply:
dezembro 5th, 2008 at 3:57
Obrigado pelas palavras. Incentivam muito o nosso trabalho.
Reply
Comentário feito em dezembro 4th, 2008 às 10:15
Testei a rotina e funcionou perfeitamente, inclusive com acréscimo de novos campos de pesquisa. Ficou uma dúvida: caso queira pesquisar numa única coluna, como fazer? Se não estou enganado, o FIND pesquisa na planilha toda.
Reply
exceldoseujeito Reply:
dezembro 22nd, 2008 at 2:39
Você pode pesquisar em colunas específicas sim. No exemplo, ensinei a pesquisar na planilha inteira. Eu usei Plan1.Cells. Para pesquisar em uma coluna só, você deve definir a coluna desejada na expressão do método find. Exemplo: Para pesquisar na coluna A, digite: Plan1.Range(“A:A”).Find. Entendeu?! Assim a pesquisa será feita apenas na coluna A.
Um abraço.
Reply
Comentário feito em dezembro 22nd, 2008 às 2:00
As informações deste site são as mais úteis e diretas que já encontrei. Grato pelo pronto retorno.
Reply
Comentário feito em dezembro 26th, 2008 às 14:05
Tenho feito muito bom uso desta pesquisa. Como sempre, as necessidades avançam: que modificações devo introduzir para que a pesquisa colete dados de outra pasta de trabalho diferente daquela onde está o form? É possível?
Reply
exceldoseujeito Reply:
abril 26th, 2009 at 12:56
É possivel sim. Basta informar a região onde será feita a pesquisa. No exemplo, eu referencio a Plan1. Você pode alterar para a planila que desejar.
Abraço.
Reply
Comentário feito em março 21st, 2009 às 23:47
O espinButton não funciona, fica ativado porem não segue para frente nem para trás o que está errado?
Reply
exceldoseujeito Reply:
abril 26th, 2009 at 12:15
Sem ver o seu arquivo fica difícil saber o que deu errado. Pode ser alguma falha de digitação. Uma simples aspas inserida de forma incorreta já impossibilita a boa eficácia do código.
Reply
Comentário feito em abril 10th, 2009 às 15:43
È bom porém o spinButton não funcionou, ele fica ativado mas não vai para frente nem para trás, o que deve estar errado!
Nilson
Reply
exceldoseujeito Reply:
abril 26th, 2009 at 12:17
Respondido no tópico anterior
Reply
Comentário feito em abril 10th, 2009 às 15:50
Com Relação a este trecho do código comentado pelo Reinaldo, eu digitei e deu erro. Posteriormente Copiei e colei somente esta parte e funcionou.
Set Busca=Plan1.Cells.Find(What:=TermoPesquisado,After:=Range(”A1″), LookIn:=xlFormulas,_
LookAt:=xlPart,SearchOrder:=xlByRows,SearchDirection:=xlNext,_
MatchCase:=False, SearchFormat:=False)
Uma das coisas que Tive Duvida é após ajustar o Desing manda clicar em F7 e declarar duas variaveis, sendo que clicando em F7 a Tela já aparece para criar um UseForm. Fiquei indeciso se era para digitar ali mesmo ou não.
O Que eu Gostaria agora é de acessar novamente o código do UserForm Para Fazer umas Modificações, mas não estou conseguindo.
Reply
exceldoseujeito Reply:
maio 6th, 2009 at 19:58
Haroldo,
Como disse em resposta ao Reinaldo, se você tiver copiado no site e colado no código, alguns caracteres HTML podem não ser no mesmo padrão que um texto puro, exemplo disso são as “aspas duplas”, sempre dá erro nelas. Enfim, tente digitar o código manualmente.
Quanto a sua dúvida da exibição do código, a tecla F7 é um tecla de atalho para acesso ao código do objeto que estiver ativo no momento do seu pressionamento. Alternativamente, você pode exibir o código do formulário, por exemplo, clicando em seu interior com o botão direito do mouse, em seguida, clicando na opção “Exibir código”. Outra maneira é acessar a opção “Exibir” no menu principal e selecionar a opção “Código”.
Um abraço
Reply
Comentário feito em maio 6th, 2009 às 18:47
Conforme o Nilson Disse, Realmente o SpinButton não funcionou comigo também.
Reply
exceldoseujeito Reply:
maio 6th, 2009 at 20:01
Haroldo,
Fica difícil prever o que possa estar ocorrendo no seu projeto sem ter acesso a sua planilha.
Sugestão: Baixe o arquivo fonte que disponibilizei aqui no site e faça os testes.
Baixar Arquivo de Exemplo
Um abraço.
Reply
Comentário feito em maio 6th, 2009 às 18:47
meu caro amigo…
esta dica de formulário acabou por resolver um grande problema meu… muito obrigado….
estou doido para que vc poste uma nova busca para buscar mais de um termo…
mas muito obrigado mesmo :D
Reply
Comentário feito em junho 30th, 2009 às 18:05
Boa tarde!Em primeiro lugar, parabéns pelo site!Muito bem explicado.Eu gstaria de saber se existe algum método de as cores da planilha não sairem na hora da impressão.Gostaria de as visualizar colorida na tela, mas na hora da impressão as cores não sairem.Desde já muito agradecido.
Reply
exceldoseujeito Reply:
julho 30th, 2009 at 17:57
Obrigado.
Para imprimir em preto e branco, você pode clicar Arquivo > Configurar Página…
Em seguida, selecione a aba “Planilha” e marque a opção “Preto e branco” do bloco Imprimir.
Clique em Ok e pronto.
Abraços
Reply
Comentário feito em julho 30th, 2009 às 15:08
Olá
Estava pensando em utilizar modelo semalhante em nossos controles aqui na empresa, porém antes das adaptações tentei exatamente o que descreve acima, e ao clikar no botão de comando, abre o formulário porém não funciona. também não dá mensagem de erro. apenas não funciona. favor enviar e-mail para contato, assim lhe mando meu arquivo para que vc possa verificar o que está errado.
Abraços…
Reply
exceldoseujeito Reply:
agosto 25th, 2009 at 13:10
Olá Edmar,
Vários motivos podem causar a inoperância do código. Alguns deles eu já descrevi nos comentários deste artigo. Depois dê uma olhadinha neles, podem esclarecer sua dúvida.
As macros podem estar desativadas: ative-as através das opções de segurança. Algum erro de digitação no código: tipo, caso vc tenha copiado e colado o código diretamente do site, as aspas não são compreendidas pelo compilador, tente substituí-las. Enfim, existem n possibilidades.
Mas se quiser, enviar uma planilha para nós, acesse o link Orçamento gratuito. Inclusive, aproveito para dizer que desenvolvemos projetos de acordo com a proposta do cliente, automatizando aos mais diversos tipos e modelos de planilhas com ótimos preços. E o orçamento, como disse, é gratuito.
Abraço.
Reply
Comentário feito em agosto 25th, 2009 às 10:44
DIZ TUDO CONFORME ORIENTADO MAS DA ERRO SpinButton1 E MANDA DEPURAR
Private Sub SpinButton1_Change()
Dim Linha As Long
Dim TotalOcorrencias As Long
TotalOcorrencias = SpinButton1.Max + 1
Linha = MatrizResultados(SpinButton1.Value)
Label_Registros_Contador.Caption = SpinButton1.Value + 1 & ” de ” & TotalOcorrencias
TextBox1.Text = Plan1.Cells(Linha, 1).Value
TextBox2.Text = Plan1.Cells(Linha, 2).Value
TextBox3.Text = Plan1.Cells(Linha, 3).Value
TextBox4.Text = Plan1.Cells(Linha, 4).Value
End Sub
Reply
Comentário feito em outubro 9th, 2009 às 2:40
Olá,
Cara está dica é D+ 100%, queria saber só um pouquinho do que vc sabe.
Estou vendo as outras dicas e são TOP.
Não consegui escrever o vba mas vi que vc postou acima, e funciona 100%,
até localizei onde eu estava errando.
Attenciosamente
GGB
Reply
Comentário feito em outubro 10th, 2009 às 22:31
Aqui Portugal
Gostei muito deste site. É EXCEL mesmo do meu jeito. Obrigado
Cesar
Reply
Comentário feito em novembro 2nd, 2009 às 14:42
Muito boa as explicacoes deste site, tenho uma duvida, como fazer qdo o meu campo de pesquisa for data, eu tenho q mudar alguma coisa_>_? muito obrigado
Reply
Reinaldo Coral Reply:
dezembro 31st, 2009 at 14:51
Não é necessário alterações para buscar por data.
Reply
Comentário feito em dezembro 20th, 2009 às 12:03
Estou perguntando por que eu fiz as mudancas e esta dando erro aqui._?:
Set Busca = Ficha.Cells.Find(What:=TermoPesquisado, After:=Range(“B43″), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
acho q na ultima linha, pelo menos e onde fica o ponteiro.
Reply
Reinaldo Coral Reply:
dezembro 31st, 2009 at 14:50
Não identifiquei erro acima.
Reply
Comentário feito em dezembro 20th, 2009 às 12:06
Estou estudando esse material a bastante tempo, e neste momento estou usando ele para gerar um protocolo de serviço buscando dados em outro planilha.
O recurso que estou usando é esse:
‘Abra a unidade c: e o diretório SAEW
ChDir “C:\saew”
‘Abre o arquivo saetab.dbf e copia todos os dados
Workbooks.Open Filename:=”C:\saew\saetab.dbf”, Notify:=True
Cells.Select
Selection.Copy
Windows(“buca Personalizada.xls”).Activate
Sheets(“DADOS”).Select
Cells.Select
Range(“a1″).Activate
ActiveSheet.Paste
‘Fecha o arquivo SAETAB
Windows(“SAETAB.DBF”).Activate
ActiveWindow.Close
Reply
Comentário feito em janeiro 29th, 2010 às 15:14
Formula de busca muito bem elaborada. Comigo esta funcionando tudo corretamente.
Muito obrigado Reinaldo, por compartilhar conosco as suas habilidades e conhecimento.
Reply
Reinaldo Coral Reply:
janeiro 29th, 2010 at 22:06
Nilton,
É um prazer.
Abçs
Reply
Comentário feito em janeiro 29th, 2010 às 15:19
Deixar um comentário