Loop through listbox rows if column is not empty copy paste to empty row in sheet

Question

I'm trying to loop through listbox(attached picture below), and if the column(3) has value, then copy & paste row to empty row in excel sheet for later use. I wanted to copy the row using column value to put it in sheet, but it just copys last row value, and repeats. Could you please point out what I did wrong in the below code?

TIA

s = 22

For i = 0 To Me.AnBox.ListCount - 1

    If Me.AnBox.Column(3) <> "" Then

        Sheets("SparePartsRequestForm").Range("A" & s).Value = Me.AnBox.Column(2)
        Sheets("SparePartsRequestForm").Range("C" & s).Value = Me.AnBox.Column(1)
        Sheets("SparePartsRequestForm").Range("D" & s).Value = Me.AnBox.Column(3)
        s = s + 1

    End If

Next i

Userform

Part of excel sheet

Answer 1

Few errors:

• The index of your ListBox.Column is zero based, so you not looking at the third but second Index.
• You are accessing the values wrongly, trying to read a full column so it seems. The correct syntax is expression.Column(pvargColumn, pvargIndex) so you are missing a parameter. Check the documentation remarks to see the difference between using the second parameter or not.
• Make use of the iteration and the more common List property to access each row individually.

So therefor your code could look like:

s = 22
For i = 0 To Me.AnBox.ListCount - 1
    If Me.AnBox.List(i, 2) <> "" Then
        Sheets("SparePartsRequestForm").Range("A" & s).Value = Me.AnBox.List(i, 1)
        Sheets("SparePartsRequestForm").Range("C" & s).Value = Me.AnBox.List(i, 0)
        Sheets("SparePartsRequestForm").Range("D" & s).Value = Me.AnBox.List(i, 2)
        s = s + 1 
    End If
Next i

It is possible through the Column property too though:

s = 22
For i = 0 To Me.AnBox.ListCount - 1
    If Me.AnBox.Column(2, i) <> "" Then
        Sheets("SparePartsRequestForm").Range("A" & s).Value = Me.AnBox.Column(1, i)
        Sheets("SparePartsRequestForm").Range("C" & s).Value = Me.AnBox.Column(0, i)
        Sheets("SparePartsRequestForm").Range("D" & s).Value = Me.AnBox.Column(2, i)
        s = s + 1 
    End If
Next i

_{Note: If your intention is to paste at the next available row, there are great ways to return the last used row of a range. See here for example}