como user uma textbox para pesquisar em toda a colunas da planilha

peculio

21-03-2022 20:27:15

ola galera,

tenho outra duvida,

estou usando 3 textbox para pesquisar em tres colunas diferentes, tem como eu usar apenas uma textbox e ela pesquisar em todas as colunas da planilha?

desde ja agradeço.

webmaster

21-03-2022 21:49:54

Tem algo pronto? Pode postar o código?

peculio

22-03-2022 13:25:06

Planilha1.Select



Dim Lin As Integer, LinBox As Integer
Dim Busca As Integer, Contador As Integer
Lin = 1
LinBox = 0
Contador = 0

ListBox_REGISTROS.Clear

Do While Lin < 30000
Busca = InStr(Cells(Lin, 6).Value, BARRA_PESQUISA)
If Busca > 0 Then
With ListBox_REGISTROS
.AddItem
.List(LinBox, 0) = Cells(Lin, 5)
.List(LinBox, 1) = Cells(Lin, 6)
.List(LinBox, 2) = Cells(Lin, 7)
.List(LinBox, 3) = Cells(Lin, 8)
.List(LinBox, 4) = Cells(Lin, 9)
.List(LinBox, 5) = Cells(Lin, 10)
.List(LinBox, 6) = Cells(Lin, 13)
.List(LinBox, 7) = Cells(Lin, 15)
.List(LinBox, 8) = Cells(Lin, 16)
.List(LinBox, 9) = Cells(Lin, 17)
End With

LinBox = LinBox + 1
Contador = Contador + 1
End If
Lin = Lin + 1
Loop


CONTADOREG = Contador



End Sub

Basole

22-03-2022 17:26:06

Com a função FindAll by Chip Peasron
que faz a busca em toda a aba

Exemplo:

Código: Selecionar todos

Private Sub FindAllMatches()
Dim SearchRange     As Range
Dim FindWhat        As Variant
Dim FoundCells      As Range
Dim FoundCell       As Range
Dim lSearchCol      As Long
Dim lLastRow        As Long
Dim ws              As Worksheet
Dim msg             As String

Set ws = ThisWorkbook.Sheets("Planilha1")
    If VBA.Len(Me.TextBox1.Value) > 1 Then        
        Set SearchRange = ActiveSheet.UsedRange.Cells        
        FindWhat = Me.TextBox1.Value        
        Set FoundCells = FindAll(SearchRange:=SearchRange, _
                                FindWhat:=FindWhat, _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByColumns, _
                                MatchCase:=False, _
                                BeginsWith:=vbNullString, _
                                EndsWith:=vbNullString, _
                                BeginEndCompare:=vbTextCompare)                                
        If FoundCells Is Nothing Then              
                MsgBox "Nenhum Registro Encontrado com o Termo:  " & Me.TextBox1.Text
        Else           
          With Me.ListBox1
            .Clear                  
            For Each FoundCell In FoundCells
                .AddItem
                .List(i, 0) = ws.Cells(FoundCell.Row, 1)
                .List(i, 1) = ws.Cells(FoundCell.Row, 2)
                .List(i, 2) = ws.Cells(FoundCell.Row, 3)
                .List(i, 3) = ws.Cells(FoundCell.Row, 4)
                .List(i, 4) = ws.Cells(FoundCell.Row, 5)
                .List(i, 5) = ws.Cells(FoundCell.Row, 6)
                .List(i, 6) = ws.Cells(FoundCell.Row, 7)
                .List(i, 7) = ws.Cells(FoundCell.Row, 8)
                .List(i, 8) = ws.Cells(FoundCell.Row, 9)
                .List(i, 9) = ws.Cells(FoundCell.Row, 10)
                i = i + 1
            Next FoundCell
          End With          
        End If        
    Else        
        Me.ListBox1.Clear    
 End If
        
End Sub

Código: Selecionar todos

'By Chip Peasron, chip@cpearson.com. www.cpearson.com
Function FindAll(SearchRange As Range, _
                FindWhat As Variant, _
                Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False, _
                Optional BeginsWith As String = vbNullString, _
                Optional EndsWith As String = vbNullString, _
                Optional BeginEndCompare As VbCompareMethod = vbTextCompare) As Range


Dim FoundCell As Range
Dim FirstFound As Range
Dim LastCell As Range
Dim ResultRange As Range
Dim XLookAt As XlLookAt
Dim Include As Boolean
Dim CompMode As VbCompareMethod
Dim Area As Range
Dim MaxRow As Long
Dim MaxCol As Long
Dim BeginB As Boolean
Dim EndB As Boolean


CompMode = BeginEndCompare
If BeginsWith <> vbNullString Or EndsWith <> vbNullString Then
    XLookAt = xlPart
Else
    XLookAt = LookAt
End If

For Each Area In SearchRange.Areas
    With Area
        If .Cells(.Cells.Count).Row > MaxRow Then
            MaxRow = .Cells(.Cells.Count).Row
        End If
        If .Cells(.Cells.Count).Column > MaxCol Then
            MaxCol = .Cells(.Cells.Count).Column
        End If
    End With
Next Area
Set LastCell = SearchRange.Worksheet.Cells(MaxRow, MaxCol)

On Error GoTo 0
   
Set FoundCell = SearchRange.Find(What:=FindWhat, _
        After:=LastCell, _
        LookIn:=LookIn, _
        LookAt:=XLookAt, _
        SearchOrder:=SearchOrder, _
        MatchCase:=MatchCase)

If Not FoundCell Is Nothing Then
    Set FirstFound = FoundCell
  
    Do Until False
        Include = False
        If BeginsWith = vbNullString And EndsWith = vbNullString Then
            Include = True
        Else
            If BeginsWith <> vbNullString Then
                If StrComp(Left(FoundCell.Text, Len(BeginsWith)), BeginsWith, BeginEndCompare) = 0 Then
                    Include = True
                End If
            End If
            If EndsWith <> vbNullString Then
                If StrComp(Right(FoundCell.Text, Len(EndsWith)), EndsWith, BeginEndCompare) = 0 Then
                    Include = True
                End If
            End If
        End If
        If Include = True Then
            If ResultRange Is Nothing Then
                Set ResultRange = FoundCell
            Else
                Set ResultRange = Application.Union(ResultRange, FoundCell)
            End If
        End If
        Set FoundCell = SearchRange.FindNext(After:=FoundCell)
        If (FoundCell Is Nothing) Then
            Exit Do
        End If
        If (FoundCell.Address = FirstFound.Address) Then
            Exit Do
        End If

    Loop
End If
    
Set FindAll = ResultRange

End Function

peculio

22-03-2022 19:32:56

ENTENDI POREM VEIO DOIS CODIGO QUAIS EU USO NÃO ENTENDI.

Basole

22-03-2022 20:03:17

Voce tem que adaptar ao seu cenário.
Mas já esta quase mastigado, voce precisa alterar os nomes dos controles, listbox textbox, e respct. colunas

No evento do seu botão ou no evento change da textbox pesquisa,
coloque uma chamada a sub FindAllMatches

Exemplo:

Código: Selecionar todos

Private Sub CommandButton1_Click()
   call FindAllMatches
End Sub

peculio

23-03-2022 13:04:54

ok obrigado.

peculio

23-03-2022 13:12:53

bom dia a primeira função ue voce me passou deu uma resposta dessa maneira. sub ou função não definida. no trecho "FindAll"

(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
MsgBox "Nenhum Registro Encontrado com o Termo: " & Me.geral.TEXT



porem meu codigo ficou assim sera que esqueci de alterar algo?

Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range
Dim lSearchCol As Long
Dim lLastRow As Long
Dim ws As Worksheet
Dim msg As String
Dim Lin As Integer

Lin = 1


Set ws = ThisWorkbook.Sheets("Planilha1")
If VBA.Len(Me.geral.Value) > 1 Then
Set SearchRange = ActiveSheet.UsedRange.Cells
FindWhat = Me.geral.Value
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
MsgBox "Nenhum Registro Encontrado com o Termo: " & Me.geral.TEXT
Else
With Me.ListBox_REGISTROS
.Clear
For Each FoundCell In FoundCells
.AddItem
.List(LinBox, 0) = Cells(Lin, 5)
.List(LinBox, 1) = Cells(Lin, 6)
.List(LinBox, 2) = Cells(Lin, 7)
.List(LinBox, 3) = Cells(Lin, 8)
.List(LinBox, 4) = Cells(Lin, 9)
.List(LinBox, 5) = Cells(Lin, 10)
.List(LinBox, 6) = Cells(Lin, 13)
.List(LinBox, 7) = Cells(Lin, 15)
.List(LinBox, 8) = Cells(Lin, 16)
.List(LinBox, 9) = Cells(Lin, 17)

LinBox = LinBox + 1
Next FoundCell
End With
End If
Else
Me.ListBox_REGISTROS.Clear
End If


End Sub

Basole

23-03-2022 18:36:20

No userform voce insira o codigo abaixo,
*Altere o nome do botão se necessario

Código: Selecionar todos

Private Sub CommandButton1_Click()
 Call FindAllMatches
End Sub

Private Sub FindAllMatches()
Dim SearchRange     As Range
Dim FindWhat        As Variant
Dim FoundCells      As Range
Dim FoundCell       As Range
Dim lSearchCol      As Long
Dim lLastRow        As Long
Dim ws              As Worksheet
Dim LinBox!

Set ws = ThisWorkbook.Sheets("Planilha1")

    If VBA.Len(Me.Geral.Text) > 1 Then
    
        Set SearchRange = ActiveSheet.UsedRange.Cells
        FindWhat = Me.Geral.Text
        Set FoundCells = FindAll(SearchRange:=SearchRange, _
                                FindWhat:=FindWhat, _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByColumns, _
                                MatchCase:=False, _
                                BeginsWith:=vbNullString, _
                                EndsWith:=vbNullString, _
                                BeginEndCompare:=vbTextCompare)
        If FoundCells Is Nothing Then
                MsgBox "Nenhum Registro Encontrado com o Termo:  " & Me.Geral.Text
        Else
          With Me.ListBox_REGISTROS
            .Clear
            For Each FoundCell In FoundCells
                .AddItem
                .List(LinBox, 0) = ws.Cells(FoundCell.Row, 5)
                .List(LinBox, 1) = ws.Cells(FoundCell.Row, 6)
                .List(LinBox, 2) = ws.Cells(FoundCell.Row, 7)
                .List(LinBox, 3) = ws.Cells(FoundCell.Row, 8)
                .List(LinBox, 4) = ws.Cells(FoundCell.Row, 9)
                .List(LinBox, 5) = ws.Cells(FoundCell.Row, 10)
                .List(LinBox, 6) = ws.Cells(FoundCell.Row, 13)
                .List(LinBox, 7) = ws.Cells(FoundCell.Row, 15)
                .List(LinBox, 8) = ws.Cells(FoundCell.Row, 16)
                .List(LinBox, 9) = ws.Cells(FoundCell.Row, 17)
                LinBox = LinBox + 1
            Next FoundCell
          End With
        End If
    Else
        Me.ListBox_REGISTROS.Clear
 End If
        
End Sub

E em um modulo padrão a função findAll, abaixo:

Código: Selecionar todos

Function FindAll(SearchRange As Range, _
                FindWhat As Variant, _
                Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False, _
                Optional BeginsWith As String = vbNullString, _
                Optional EndsWith As String = vbNullString, _
                Optional BeginEndCompare As VbCompareMethod = vbTextCompare) As Range


Dim FoundCell As Range
Dim FirstFound As Range
Dim LastCell As Range
Dim ResultRange As Range
Dim XLookAt As XlLookAt
Dim Include As Boolean
Dim CompMode As VbCompareMethod
Dim Area As Range
Dim MaxRow As Long
Dim MaxCol As Long
Dim BeginB As Boolean
Dim EndB As Boolean


CompMode = BeginEndCompare
If BeginsWith <> vbNullString Or EndsWith <> vbNullString Then
    XLookAt = xlPart
Else
    XLookAt = LookAt
End If

For Each Area In SearchRange.Areas
    With Area
        If .Cells(.Cells.Count).Row > MaxRow Then
            MaxRow = .Cells(.Cells.Count).Row
        End If
        If .Cells(.Cells.Count).Column > MaxCol Then
            MaxCol = .Cells(.Cells.Count).Column
        End If
    End With
Next Area
Set LastCell = SearchRange.Worksheet.Cells(MaxRow, MaxCol)

On Error GoTo 0
   
Set FoundCell = SearchRange.Find(What:=FindWhat, _
        After:=LastCell, _
        LookIn:=LookIn, _
        LookAt:=XLookAt, _
        SearchOrder:=SearchOrder, _
        MatchCase:=MatchCase)

If Not FoundCell Is Nothing Then
    Set FirstFound = FoundCell
  
    Do Until False
        Include = False
        If BeginsWith = vbNullString And EndsWith = vbNullString Then
            Include = True
        Else
            If BeginsWith <> vbNullString Then
                If StrComp(Left(FoundCell.Text, Len(BeginsWith)), BeginsWith, BeginEndCompare) = 0 Then
                    Include = True
                End If
            End If
            If EndsWith <> vbNullString Then
                If StrComp(Right(FoundCell.Text, Len(EndsWith)), EndsWith, BeginEndCompare) = 0 Then
                    Include = True
                End If
            End If
        End If
        If Include = True Then
            If ResultRange Is Nothing Then
                Set ResultRange = FoundCell
            Else
                Set ResultRange = Application.Union(ResultRange, FoundCell)
            End If
        End If
        Set FoundCell = SearchRange.FindNext(After:=FoundCell)
        If (FoundCell Is Nothing) Then
            Exit Do
        End If
        If (FoundCell.Address = FirstFound.Address) Then
            Exit Do
        End If

    Loop
End If
    
Set FindAll = ResultRange

End Function

peculio

24-03-2022 12:22:09

bom dia, eu inseri as macros do jeito que me passou porem,

nesta linha (Set ws = ThisWorkbook.Sheets("Planilha1") porem o erro que dá é subincrito fora do intervalo

Basole

25-03-2022 19:46:37

peculio escreveu: ↑Qui Mar 24, 2022 9:22 am nesta linha (Set ws = ThisWorkbook.Sheets("Planilha1") porem o erro que dá é subincrito fora do intervalo
Como eu havia dito, voce precisa adptar o exemplo ao seu cenario.
O erro é porque a aba Planilha1, não esta presente na sua Pasta_de_trabalho.
Tem que alterar colocando no nome da aba que será pesquiado.

peculio

01-04-2022 17:19:10

Entendi, mas o meu ambiente de trabalho é justamente na planilha 1

Basole

01-04-2022 17:38:40

Mas "planilha 1" é diferente de "planilha1" para o VBA o espaço entre caracteres é considerado. Então esse pequenos detalhes pode gerar erros.

Tente setar pelo índice da planilha. Se a mesma estiver na posição 1 será ajustado corretamente.
Exemplo:

Código: Selecionar todos

Set ws = ThisWorkbook.Sheets(1)

joseA

04-04-2022 13:52:16

Se anexasse sua planilha ou uma parte dela teria uma resposta mais precisa. Na sua primeira solicitação se acrescentasse um Combobox para escolher a coluna a ser pesquisada conseguiria sucesso