Excel VBA pulling multiple columns to list box when searching Worksheet-CodePudding

I'm having trouble figuring out how to get the data on either side of a cell (searching column B and also need data in columns A & C) to also populate in a list box when preforming a search.

    Dim iSheet As Worksheet
    Dim iBook As Workbook
    Set iBook = Application.ThisWorkbook
    Set iSheet = iBook.Sheets("Bin13In")
    Dim A
    Dim firstAddress
    Me.ListBox1.Clear

    With iSheet.Range("B1:B14000")
    Set A = .Find(TextBox5.Text, LookIn:=xlValues, LookAt:=xlPart)
    If Not A Is Nothing Then
    firstAddress = A.Address
    
    Do
        Me.ListBox1.AddItem A.Text
        Set A = .FindNext(A)
    Loop While Not A Is Nothing And A.Address <> firstAddress
    Else
    MsgBox "Not found"
    End If
    End With

I've tried a few different things but they all seem to be completely wrong

CodePudding user response：

There are several ways to fill a ListBox with multiple column values.

One method is to write the data into a 2-dimensional array and assign that array to the List-property of the ListBox:

Dim myArr
myArr = iSheet.Range("A2:D10")
Me.ListBox1.List = myArr

However, in your case it is a little bit tricky as you don't know in advance how many rows you will have. That leads to the situation where you need to use Redim Preserve for every row. And Redim is possible only for the last dimension of an array, therefore you need to have the column as first index and the row as second, while the list needs it with row as first index and column as second. To solve that, you need to transpose the array when assigning it to the list.

ReDim myArr(1 To 3, 1 To 1)
hits = 0
Set A = .Find(TextBox5.Text, LookIn:=xlValues, LookAt:=xlPart)
If Not A Is Nothing Then
    firstAddress = A.Address
    Do
        hits = hits   1
        If UBound(myArr, 2) < hits Then ReDim Preserve myArr(1 To 3, 1 To hits)
        myArr(1, hits) = A.Value
        myArr(2, hits) = A.Offset(0, -1).value
        myArr(3, hits) = A.Offset(0,  1).value
    Set A = .FindNext(A)
    Loop While Not A Is Nothing And A.Address <> firstAddress
Next
Me.ListBox1.List = Application.Transpose(myArr)

An alternative is to stick to your code and write the extra column values using the .List property with an index. You just need to know that the index for both the row and the column starts with 0, not with 1:

Do
    With Me.ListBox1
        .AddItem A.Text
        .List(.ListCount - 1, 1) = A.Offset(0, -1)  
        .List(.ListCount - 1, 2) = A.Offset(0, 1)
        .FindNext(A)
    End With
Loop While Not A Is Nothing And A.Address <> firstAddress