none
Run-time Error '381': Could not set the List property. Invalid property array index. RRS feed

  • Question

  • I am using the following code to pull data from a worksheet and drop it into a listbox on a form. The code works fine if the If statement is not there, or if I remove the condition and change it to true, but with the code as written, I get the error indicated in the subject line. Any suggestions for how to fix this?

    Private Sub UserForm_Activate()
        Dim woFields(7) As String
        Dim wos() As Variant
        Dim ws As Worksheet
        Dim woRng As Range
        Dim r As Range
        Dim strTest As String
        Dim i As Integer
        Dim numRows As Integer
        Dim workRow As Integer
        Dim varTest As Variant
        
        
        Set ws = Worksheets("MaximoImport")
        Set woRng = ws.UsedRange
        numRows = ws.UsedRange.Rows.Count
        ReDim wos(numRows)
        For workRow = 2 To numRows
        
        
        If (woRng.Cells(workRow, 24) <> "X") Then
          With Me.lstBoxWos
                .AddItem
                .List(workRow - 2, 0) = woRng.Cells(workRow, 21)
                .List(workRow - 2, 1) = woRng.Cells(workRow, 4)
                .List(workRow - 2, 2) = woRng.Cells(workRow, 9)
                .List(workRow - 2, 3) = woRng.Cells(workRow, 22)
                .List(workRow - 2, 4) = woRng.Cells(workRow, 23)
                .List(workRow - 2, 5) = woRng.Cells(workRow, 5)
                .List(workRow - 2, 6) = woRng.Cells(workRow, 7)
          End With
        End If
        
        
        Next workRow
        
    End Sub
    

    Thanks,

    Ray

    Thursday, October 2, 2014 2:51 AM

Answers

  • This happens because when you are skipping a cell that has X your workRow variable increments as well. 

    So if you add row 2 and 3 and skip 4 and try to add 5 then you have got index 4 empty. 

    You could modify the for loop a bit adding an extra variable nxtItem to keep track of the index to add to

    For workRow = 2 To numRows
        
        Dim nxtItem As Long
        nxtItem = 0
        
        If (UCase(woRng.Cells(workRow, 24)) <> "X") Then
          With Me.lstBoxWos
                .AddItem
                .List(nxtItem, 0) = woRng.Cells(workRow, 21)
                .List(nxtItem, 1) = woRng.Cells(workRow, 4)
                .List(nxtItem, 2) = woRng.Cells(workRow, 9)
                .List(nxtItem, 3) = woRng.Cells(workRow, 22)
                .List(nxtItem, 4) = woRng.Cells(workRow, 23)
                .List(nxtItem, 5) = woRng.Cells(workRow, 5)
                .List(nxtItem, 6) = woRng.Cells(workRow, 7)
                nxtItem = nxtItem + 1
          End With
        End If
    Next workRow
    

    and increment that variable every time a new item has been added to the list.

    Also, make sure your ListBox.Column property is set to 7.


    Thursday, October 2, 2014 11:00 AM

All replies

  • This happens because when you are skipping a cell that has X your workRow variable increments as well. 

    So if you add row 2 and 3 and skip 4 and try to add 5 then you have got index 4 empty. 

    You could modify the for loop a bit adding an extra variable nxtItem to keep track of the index to add to

    For workRow = 2 To numRows
        
        Dim nxtItem As Long
        nxtItem = 0
        
        If (UCase(woRng.Cells(workRow, 24)) <> "X") Then
          With Me.lstBoxWos
                .AddItem
                .List(nxtItem, 0) = woRng.Cells(workRow, 21)
                .List(nxtItem, 1) = woRng.Cells(workRow, 4)
                .List(nxtItem, 2) = woRng.Cells(workRow, 9)
                .List(nxtItem, 3) = woRng.Cells(workRow, 22)
                .List(nxtItem, 4) = woRng.Cells(workRow, 23)
                .List(nxtItem, 5) = woRng.Cells(workRow, 5)
                .List(nxtItem, 6) = woRng.Cells(workRow, 7)
                nxtItem = nxtItem + 1
          End With
        End If
    Next workRow
    

    and increment that variable every time a new item has been added to the list.

    Also, make sure your ListBox.Column property is set to 7.


    Thursday, October 2, 2014 11:00 AM
  • Thanks, I completely missed that!

    Thursday, October 2, 2014 1:36 PM
  • No problem :) Please consider accepting the answer if it has solved your problem so we can mark this as solved.
    Thursday, October 2, 2014 1:56 PM