How to show 2 rows in Listbox

Question

It’s a simple question, but I did some research, and I couldn’t find anything specific to my case. The code below corresponds to searching for data in a table, but when there is more than 1 corresponding value, it replaces the last found value. What I would like is for him to add a line as the example below:

**The example is unrelated to the code

Table

AAA 111 222 333

AAA 111 555 777

Look for: AAA

Result currently:

AAA 111 555 777(replaced by AAA 222 333)

What I would like:

AAA 111 222 333

AAA 111 555 777

Private Sub Pesquisar_Click()
    Dim nApol As String
    Dim nApolBanco As String
    Dim nlin As Long
    Dim Linha As Range


    Application.ScreenUpdating = False

    If Len(Me.TextBox1.Value) > 3 Then

        nApol = TextBox1.Value + TextBox2.Value + TextBox3.Value

        For nlin = 2 To Sheets("Banco de Dados").Cells(Rows.Count, "B").End(xlUp).Row

            nApolBanco = Cells(nlin, "B")

            If UCase(nApolBanco) Like nApol Then
                MsgBox ("Found")
                ListBox1.ColumnCount = 14

                ListBox1.RowSource = Range(Cells(nlin, "A"), Cells(nlin, "T")).Address

            End If

        Next
    Else
        MsgBox ("Não encontrado")
    End If

End Sub

Answer 1

Try it that way:

Private Sub Pesquisar_Click()
    Dim nApol As String
    Dim nApolBanco As String
    Dim nlin As Long
    Dim Linha As Range
    Dim arrayItems()

    Application.ScreenUpdating = False

    If Len(Me.TextBox1.Value) > 3 Then

        nApol = TextBox1.Value + TextBox2.Value + TextBox3.Value

        ListBox1.ColumnCount = Range("T:T").Column

        RowCount = Sheets("Banco de Dados").Cells(Rows.Count, "B").End(xlUp).Row

        ReDim arrayItems(1 To RowCount, 1 To Range("T:T").Column)

        lin = 1

        For nlin = 2 To RowCount

            nApolBanco = Cells(nlin, "B")

            If UCase(nApolBanco) Like nApol Then

                ListBox1.AddItem
                For coluna = 1 To Range("T:T").Column
                    arrayItems(lin, coluna) = Cells(nlin, coluna).Value
                Next coluna
                lin = lin + 1

            End If
        Next

        ListBox1.List = arrayItems()

    Else
        MsgBox ("Não encontrado")
    End If

End Sub

You create an array with the data and at the same time add lines in Listbox with ListBox1.AddItem and then add the array data.

Answer 2

Solution

A non-contiguous Range needs to be created with each desired Range and in the end fill the Listbox with this data. With ListBox.RowSource, listbox is populated with the desired range. Zeroing every time this function is called.

And matrices were used to search for the value nApol, as it decreases the interaction with the Excel Spreadsheet and is faster.

Code

Dim nApol As String
Dim nApolBanco As String
Dim nlin As Long
Dim Linha As Range
Dim ListaApol As Variant
Dim rng As Range, nova_range As Range
Dim BD As Worksheet
Set BD = ThisWorkbook.Worksheets("Banco de Dados")

Application.ScreenUpdating = False
Me.ListBox1.ColumnCount = 14
If Len(Me.TextBox1.Value) > 3 Then
    nApol = TextBox1.Value + TextBox2.Value + TextBox3.Value
    With BD
        ListaApol = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row).Value2
        For nlin = LBound(ListaApol) To UBound(ListaApol)
            nApolBanco = ListaApol(nlin, 1)
            If UCase(nApolBanco) Like nApol Then
                MsgBox ("Found")
                If rng Is Nothing Then
                    Set rng = .Range(.Cells(nlin + 1, "A"), .Cells(nlin + 1, "T"))
                Else
                    Set nova_rng = .Range(.Cells(nlin + 1, "A"), .Cells(nlin + 1, "T"))
                    Set rng = Union(rng, nova_rng)
                End If
            End If
        Next nlin
        Me.ListBox1.RowSource = rng.Address
    End With
Else
    MsgBox ("Não encontrado")
End If
Application.ScreenUpdating = True

Footsteps

1. Create the List matrix with the values of column B
2. Loop from the lowest List index value (Lbound) to the highest value (Ubound) with the variable nlin
3. Find the corresponding values
4. If it is the first time a value is found, ie, rng is Nothing, Rng is equal to the desired range. Otherwise it creates a non-contiguous range with all Ranges that have the desired value
5. Fill the Listbox with non-contiguous range values rng

The function ListBox.AddItem has a limit of 10 columns, as 14 columns are used, the non-contiguous range solution is the most recommended.