Error with code searching database sheet returning to list box

[StartingOut]

Hi, I have this code to search my "Membership Sales" worksheet. but it will not run and highlights .List(.ListCount - 1, 11) = c.Offset(0, (x + 10)).Value and returns the error "could not set the list property" it seams that if I comment that line out it simply fails on the next line as if the rows starting in the double digits are not right.

I'm just not seeing the problem.

"With Me.ListBox1
.AddItem c.Value
.List(.ListCount - 1, 0) = c.Offset(0, x).Address 'record number
.List(.ListCount - 1, 1) = c.Offset(0, x).Value 'surname
.List(.ListCount - 1, 2) = c.Offset(0, (x + 1)).Value 'first name
.List(.ListCount - 1, 3) = c.Offset(0, (x + 2)).Value 'DOB
.List(.ListCount - 1, 4) = c.Offset(0, (x + 3)).Value 'address
.List(.ListCount - 1, 5) = c.Offset(0, (x + 4)).Value 'date spoken to
.List(.ListCount - 1, 6) = c.Offset(0, (x + 5)).Value 'notes
.List(.ListCount - 1, 7) = c.Offset(0, (x + 6)).Value
.List(.ListCount - 1, 8) = c.Offset(0, (x + 7)).Value
.List(.ListCount - 1, 9) = c.Offset(0, (x + 8)).Value
.List(.ListCount - 1, 10) = c.Offset(0, (x + 9)).Value
.List(.ListCount - 1, 11) = c.Offset(0, (x + 10)).Value
.List(.ListCount - 1, 12) = c.Offset(0, (x + 11)).Value
.List(.ListCount - 1, 13) = c.Offset(0, (x + 12)).Value
.List(.ListCount - 1, 14) = c.Offset(0, (x + 13)).Value
.List(.ListCount - 1, 15) = c.Offset(0, (x + 14)).Value
.List(.ListCount - 1, 16) = c.Offset(0, (x + 15)).Value
.List(.ListCount - 1, 17) = c.Offset(0, (x + 16)).Value
.List(.ListCount - 1, 18) = c.Offset(0, (x + 17)).Value
.List(.ListCount - 1, 19) = c.Offset(0, (x + 18)).Value
.List(.ListCount - 1, 20) = c.Offset(0, (x + 19)).Value
.List(.ListCount - 1, 21) = c.Offset(0, (x + 20)).Value


End With"

 

[Jerry Sullivan]

Ahh, I didn't notice that in my testing because I was using test data with more than one matches.

A quick fix would be to re-size the array to show 2 rows minimum. The second row will be blank when there is only one match.

 '--resize array to fit records stored.
 If lRow = 1 Then lRow = 2
 ReDim Preserve vArray(1 To .Columns.Count, 1 To lRow)

The downside of this approach is that the last "blank" item in the ListBox is a selectable item, so your other code needs to handle what happens if the user clicks on it.

A better but more complex fix would be to use the .RowSource Property when one match is found, and use .List for more than one matches.
As far as I know, .AddItem and .List can't be used to populate one row of the ListBox since you have more then 10 columns.

    '--resize array to fit records stored.
    ReDim Preserve vArray(1 To .Columns.Count, 1 To lRow)
End With

'--place the array in the listbox
With Me.ListBox1
    .ColumnCount = UBound(vArray, 1)
    .ColumnWidths = "50;;80;100" 'use this property to override default col widths
    If lRow > 1 Then
        .RowSource = ""
        .List = Application.Transpose(vArray)
    Else
        .Clear
        .RowSource = Intersect(Sheets("Membership Sales") _
            .Range(sFirstAddr).EntireRow, rMyData).Address
    End If
End With

 

[StartingOut]

Jerry, that was is it. it works as it should now. Thank you very much for the rewrite. I am still a little green on the learning but this is a great way for me to compare my original code that wasn't working and see how it should have been.