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