0

I am getting an error as "Could not set list properly while filling data in list box. Please assist.

ListBox1.Clear

Dim vertical As String
lstRw = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

Dim j As Integer
j = 0

For i = 2 To lstRw
If ComboBox4.Value = ThisWorkbook.Sheets(1).Range("A" & i).Value Then

Me.ListBox1.AddItem ThisWorkbook.Sheets(1).Range("A" & i).Value

Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("B" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("C" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("D" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("E" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("F" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("G" & i).Value
j = j + 1
End If

Next i
2
  • At what line do you receive this error? Is there a reason for j being a variable? Is there another loop? Commented Oct 24, 2017 at 13:41
  • I am getting error on Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("B" & i).Value..... It is not updating any column except column "A".... Commented Oct 24, 2017 at 13:44

1 Answer 1

1

You need to specify the ColumnCount for your listbox. You also need to increment the variable j between each column being added. The variable j also needs to be reset to 1 as each new row is added:

Sub TestListBox()
    Dim lstRw As Integer
    Dim vertical As String
    Dim i As Integer
    Dim j As Integer
    lstRw = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    ListBox1.ColumnCount = 7
    ListBox1.Clear
    For i = 2 To lstRw
        If ComboBox4.Value = ThisWorkbook.Sheets(1).Range("A" & i).Value Then
            Me.ListBox1.AddItem ThisWorkbook.Sheets(1).Range("A" & i).Value
            j = 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("B" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("C" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("D" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("E" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("F" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("G" & i).Value
        End If
    Next i
End Sub
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.