VBA- Add entire row in a Listbox

Question

I’m trying to add an entire line to a listbox, based on the result of a search .Find, the idea is to find the value that matches the searched term it adds each line that contains this value in the listbox.

I tried some ways that didn’t work out.

Private Sub CommandButton2_Click()

    Dim a As Range
    Dim pesquisa As Range
    Dim valor As String
    Dim resultado As Range
    Dim resultadoAnterior As Range
    Dim intervalo As Range
    Dim linha As Range


    Set intervalo = Range("B1:B100")

    With frmPesquisa

        valor = Me.TextBox1.Value + "*"

        Set resultado = intervalo.Find(valor, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns)

        Do

            Set resultadoAnterior = resultado
            Set resultado = intervalo.Find(valor, After:=resultadoAnterior, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns)


            linha = resultadoAnterior.EntireRow


            ListBox1.AddItem (linha)

        Loop Until resultado.Row < resultadoAnterior.Row

    End With

End Sub

Answer 1

I hope I helped, returns the line number and not all the line content, since the function Entirerow does not offer this possibility. I suggest a fine return of the number of the line in which it gives macth, and this function returns all the line or values contained in it. Follows the code...

Private Sub CommandButton1_Click()
    Dim a As Range
    Dim pesquisa As Range
    Dim valor As String
    'Dim resultado As Range
    'Dim resultadoAnterior As Range
    Dim intervalo As Range
    Dim linha As Range
    Dim primeiroMtach As Integer

    Range("A1").Select
    Set intervalo = Range("B1:B100")

    ListBox1.Clear 'limpa listbox

    With frmPesquisa

        valor = Me.TextBox1.Value + "*"

        'Set resultado = intervalo.Find(valor, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns)
        Cells.Find(What:=valor, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
            :=False, SearchFormat:=False).Activate
            primeiroMtach = ActiveCell.Row
            ListBox1.AddItem (ActiveCell.Row)

        For Each celula In intervalo

            Cells.FindNext(After:=ActiveCell).Activate
            If ActiveCell.Row = primeiroMtach Then
                Exit For
            End If
            ListBox1.AddItem (ActiveCell.Row)

        Next

    End With
End Sub