Listbox For Loop

[amanphilip]

Hello Good Day to All,

I am trying to populate a listbox after doing a search. I was able to do it however i want my code to be more efficient somehow the solution I am thinking is to do a loop to add the details in the listbox. However I am getting an error "Could not set the list property. Invalid Property value error". Hope you can help me please.

  If Not UserForm2.ComboBox1.Value = "" Then
             
                        'Search for the keyword
                        search = Format(StrConv(UserForm2.ComboBox1.Value, vbUpperCase))
                        Set searchR = ws.UsedRange
                        Set foundcell = searchR.Find(What:=search, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)
                     
                        'Check if the record was found
                        If Not foundcell Is Nothing Then
                       
'
                                For i = 2 To lastrow
                                 For x = 1 To Len(ws.Cells(i, 5))
                           
                                  a = UserForm2.ComboBox1.TextLength
                                                       
                                 
                                       If InStr(1, LCase(Mid(ws.Cells(i, 14), x, a)), search, vbTextCompare) > 0 Then
                                
                               ' Below is the code that I am getting an error : could not set the list property. Invalid property value error if I use for loop.
                                
                                         UserForm2.ListBox1.AddItem ws.Cells(i, 1)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 1) = ws.Cells(i, 2)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 2) = ws.Cells(i, 3)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 3) = ws.Cells(i, 4)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 4) = ws.Cells(i, 5)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 5) = ws.Cells(i, 6)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 6) = ws.Cells(i, 7)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 7) = ws.Cells(i, 8)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 8) = ws.Cells(i, 9)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 9) = ws.Cells(i, 10)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 10) = ws.Cells(i, 11)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 11) = ws.Cells(i, 12)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 12) = ws.Cells(i, 13)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 13) = ws.Cells(i, 14)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 14) = ws.Cells(i, 15)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 15) = ws.Cells(i, 16)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 16) = ws.Cells(i, 17)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 17) = ws.Cells(i, 18)
                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 18) = ws.Cells(i, 19)
                                      
            '
                                     End If
                                 Next x
                                Next i
                    
                    
                            Set searchR = Nothing
                            Set foundcell = Nothing
                       Else
                            MsgBox "Record Not Found"
                         
                       End If

 

[ranman256]

instead of looping, have you tried filling listbox via single statement:
ListBox1.RowSource = "A1:A12"

 

[6StringJazzer]

There is a limit of 10 columns using the method you are using. You should really indicate specifically which line of code raises the error. I am going to guess it is

                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 10) = ws.Cells(i, 11)

I would suggest

ListBox1.List = ws.Range(ws.Cells(i, 1), ws.Cells(i, 19))

EDIT: On second read-through your situation is a little more complicated. You may have to build an array, then after the loop assign the array to the list.

 

[amanphilip]

instead of looping, have you tried filling listbox via single statement:
ListBox1.RowSource = "A1:A12"

Hello,

Thank you for your help, what i am trying to achieve is to list the row record to a list box that contains multiple columns. I may need much more complex code to do this. But i will look also into your suggestion.

 

[amanphilip]

There is a limit of 10 columns using the method you are using. You should really indicate specifically which line of code raises the error. I am going to guess it is

                                         UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, 10) = ws.Cells(i, 11)

I would suggest

ListBox1.List = ws.Range(ws.Cells(i, 1), ws.Cells(i, 19))

EDIT: On second read-through your situation is a little more complicated. You may have to build an array, then after the loop assign the array to the list.

Hello!

I think i have it working. I used this instead.

                         UserForm2.ListBox1.AddItem ws.Cells(i, 1)
                                   For j = 1 To 19 
                                          UserForm2.ListBox1.List(UserForm2.ListBox1.ListCount - 1, j) = ws.Cells(i, j + 1)
                                   Next j

Now i am facing performance issue with all the loops i did. I think i will have to explore the path of array to improve the performance. Thank you for your suggestion.

 

[Akuini]

Now i am facing performance issue with all the loops i did. I think i will have to explore the path of array to improve the performance. Thank you for your suggestion.

I probably could help you if you can upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here.
Also, ensure that the link is accessible to anyone.

 

[amanphilip]

I probably could help you if you can upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here.
Also, ensure that the link is accessible to anyone.

Hi Akuini,

Thank you for extending a hand. I will upload the sample workbook later today as our network restricts access to file sharing site. Appreciate it.

 

[6StringJazzer]

I think i have it working. I used this instead.

I am baffled as to why your first version did not work, but this does. Good luck with the performance issue.