Data from Listbox1 to ListBox2

[mrsec]

Hello,
I have userform that has 2 listbox's,
Listbox1 shows the data from search and by Adding command button i can select single row and transfer/move it to Listbox2
But for some reason everytime i transfer a new row from LB1 to LB2 the first one that i transfer is been overwritten by the later.
My code

Private Sub CommandButton2_Click()
    Dim i As Long
    ReDim b(1, 1 To 16)
With ListBox2
    .ColumnCount = 16
    .List = b
    .Clear
End With

    For i = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(i) = True Then
            Me.ListBox2.AddItem Me.ListBox1.List(i)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 1) = Me.ListBox1.List(i, 1)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 2) = Me.ListBox1.List(i, 2)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 3) = Me.ListBox1.List(i, 3)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 4) = Me.ListBox1.List(i, 4)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 5) = Me.ListBox1.List(i, 5)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 6) = Me.ListBox1.List(i, 6)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 7) = Me.ListBox1.List(i, 7)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 8) = Me.ListBox1.List(i, 8)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 9) = Me.ListBox1.List(i, 9)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 10) = Me.ListBox1.List(i, 10)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 11) = Me.ListBox1.List(i, 11)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 12) = Me.ListBox1.List(i, 12)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 13) = Me.ListBox1.List(i, 13)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 14) = Me.ListBox1.List(i, 14)
            Me.ListBox2.List(Me.ListBox2.ListCount - 1, 15) = Me.ListBox1.List(i, 15)
            Me.ListBox1.RemoveItem i
     Exit For
        End If
    Next
End Sub

 

[Anthony47]

Cannot test it, but I seem you have to remove that "-1"; also you may use a loop rather then repeating 15 times the same instruction.
Thus:

    For i = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(i) = True Then
            Me.ListBox2.AddItem Me.ListBox1.List(i)
            For j = 1 To 15
                Me.ListBox2.List(Me.ListBox2.ListCount, j) = Me.ListBox1.List(i, j)
            Next j
            Me.ListBox1.RemoveItem i
            Exit For
        End If
    Next

Bye

 

[mrsec]

Cannot test it, but I seem you have to remove that "-1"; also you may use a loop rather then repeating 15 times the same instruction.
Thus:

    For i = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(i) = True Then
            Me.ListBox2.AddItem Me.ListBox1.List(i)
            For j = 1 To 15
                Me.ListBox2.List(Me.ListBox2.ListCount, j) = Me.ListBox1.List(i, j)
            Next j
            Me.ListBox1.RemoveItem i
            Exit For
        End If
    Next

Bye

Thank you.yeah im having a hard time with the loop as my row contains 10+ columns.I tried removing "-1" but it doesnt work.

 

[Anthony47]

Try with your original code, not the loop; and please specify what happens.

 

[mrsec]

Try with your original code, not the loop; and please specify what happens.

For i = Me.ListBox1.ListCount - 1 To 0 Step -1

remove the "-1"from the end?

 

[Anthony47]

No, remove -1 in these instructions:
Me.ListBox2.List(Me.ListBox2.ListCount - 1, xx) = Me.ListBox1.List(i, xx)

Bye

 

[mrsec]

No, remove -1 in these instructions:
Me.ListBox2.List(Me.ListBox2.ListCount - 1, xx) = Me.ListBox1.List(i, xx)

Bye

image attached for the error

 

[Anthony47]

On which instruction? The one highlighted while in debug mode)?
(or confirm the proble is solved using dmt32 code on "the other" discussion)
Bye

 

[mrsec]

On which instruction? The one highlighted while in debug mode)?
(or confirm the proble is solved using dmt32 code on "the other" discussion)
Bye

image attached.

 

[Anthony47]

Is the problem fixed using dmt32 code in "the other" discussion?