Could you help me? ListBox executing incorrect transpose in the Worksheet3
My Command Button sends ListBox data to Sheet 2 and Sheet 3.
The ListBox has 2 columns.
Column 1 = numbers
Column 2 = Colors (Ex. "green","blue","black")
Worksheet 2 receives the data from the 2 columns of the ListBox (Column A= numbers / Column B = colors).
Worksheet 3 needs to receive ONLY the data from column 1 of the ListBox, using transpose for the columns of row 2.
It needs to happen in the following way:
Worksheet2
223 Green
224 Green
225 Green
567 Blue
568 Blue
Worksheet3
Line 2: A2=223/B2=224/C2=225/D2=567/E2=568 ...etc (regardless of how many records have in the ListBox.)
(Here, runing incorrectly.)
Error: The next records replace the previous records. They are sent on over of the previous ones. It should be sent to the next empty columns.
Below is my code:
I don't know where the error is. Help me please.
My Command Button sends ListBox data to Sheet 2 and Sheet 3.
The ListBox has 2 columns.
Column 1 = numbers
Column 2 = Colors (Ex. "green","blue","black")
Worksheet 2 receives the data from the 2 columns of the ListBox (Column A= numbers / Column B = colors).
Worksheet 3 needs to receive ONLY the data from column 1 of the ListBox, using transpose for the columns of row 2.
It needs to happen in the following way:
Worksheet2
223 Green
224 Green
225 Green
567 Blue
568 Blue
Worksheet3
Line 2: A2=223/B2=224/C2=225/D2=567/E2=568 ...etc (regardless of how many records have in the ListBox.)
(Here, runing incorrectly.)
Error: The next records replace the previous records. They are sent on over of the previous ones. It should be sent to the next empty columns.
Below is my code:
VBA Code:
Private Sub CommandButton2_Click()
'Worksheet2 ---------------(occurs correctly)
Worksheet2.Select
Worksheet2.Range("A3").Select
For i = 0 To UserForm1.ListBox1.ListCount - 1
Do
If Not (IsEmpty(ActiveCell)) Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = ListBox1.List(i, 0)
ActiveCell.Offset(0, 1).Value = ListBox1.List(i, 1)
Next
'(Worksheet3) ---------(occurs incorrectly)
Dim Lin As Long
Dim Col As Long
Lin = 2
'Worksheet2.Range("A3").Select
For Col = 0 To ThisWorkbook.Sheets("Worksheet3").Cells(Lin, Columns.Count).End(xlToRight).Value
For n = 0 To ListBox1.ListCount - 1
Col = Col + 1
ThisWorkbook.Sheets("Worksheet3").Cells(Lin, Col).Value = Application.Transpose(ListBox1.List(n))
Next
Next
End Sub
I don't know where the error is. Help me please.
Last edited by a moderator: