PDA

View Full Version : [SOLVED] Add Unequal Amount of Data To Two Columns of UserForm List Box



zoom38
01-16-2018, 08:45 PM
Good evening, does anyone know if it is possible to add an unequal amount of data to two columns of the same userform listbox? Ex. Six cells of data from sheet one into Column 1 and ten cells of data from sheet 2 into Column 2.
I've been working with the code below but can't get it right. My code only allow the same number of records in each column.


Sub TList2()
Dim LastRow As Integer
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim MyList1 As Variant
Dim MyList2 As Variant
Dim i As Long

Sheets("Sheet1").Activate
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
Sheets("Sheet2").Activate
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Row


Sheets("Sheet1").Activate
MyList1 = Sheets("Sheet1").Range(Cells(6, 3), Cells(LastRow1, 3)).Value

Sheets("Sheet2").Activate
MyList2 = Sheets("Sheet2").Range(Cells(6, 3), Cells(LastRow2, 3)).Value

CurrentT = (LastRow1 - 5) + (LastRow2 - 5)


With List.ListBox1
.ColumnCount = 2
.ColumnWidths = "130;130"
For i = 1 To LastRow1 - 5
.AddItem MyList1(i, 1)
.List(.ListCount - 1, 1) = MyList2(i, 1)
Next i
'For i = 1 To LastRow2 - 5
'.List(.ListCount - 1, 1) = MyList2(i, 1)
'Next i
.Font.Name = "Times New Roman"
.Font.Size = 12
End With
End Sub



Attached is a small spreadsheet as the example.
Thanks in advance.
Gary

SamT
01-16-2018, 11:07 PM
Maybe something like this:
Sub TList2()
Dim MyListA
Dim MyList1
Dim MyList2
Dim i As Long

MyList1 = Sheets("Sheet1").Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)).Value
MyList2 = Sheets("Sheet2").Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)).Value

ReDim MyListA(Application.WorksheetFunction.Max(UBound(MyList1), UBound(MyList2)) - 1, 2)

For i = 0 To UBound(MyListA)
MyListA(i, 0) = MyList1(i + 1)
MyListA(i, 1) = MyList2(i + 1)
Next


With List.ListBox1
.ColumnCount = 2
.ColumnWidths = "130;130"
.List = MyListA
.Font.Name = "Times New Roman"
.Font.Size = 12
End With

End Sub

zoom38
01-17-2018, 06:21 AM
Thanks for the help Sam. Unfortunately I get a "Subscript out of range" error on the line:


MyListA(i, 0) = MyList1(i + 1)


Any idea what i've done wrong?
Gary

zoom38
01-17-2018, 07:15 AM
Sam i've been working with your code and can get passed the error above but I can't get both columns to populate.

JKwan
01-17-2018, 09:05 AM
try the revised version

Sub TList2()
Dim MyListA
Dim MyList1
Dim MyList2
Dim i As Long
Sheets("Sheet1").Activate
MyList1 = Sheets("Sheet1").Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)).Value
Sheets("Sheet2").Activate
MyList2 = Sheets("Sheet2").Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)).Value

ReDim MyListA(Application.WorksheetFunction.Max(UBound(MyList1), UBound(MyList2)) - 1, 2)

For i = 0 To UBound(MyListA)
If UBound(MyList1) > i Then
MyListA(i, 0) = MyList1(i + 1, 1)
End If
If UBound(MyList2) > i Then
MyListA(i, 1) = MyList2(i + 1, 1)
End If
Next


With List.ListBox1
.ColumnCount = 2
.ColumnWidths = "130;130"
.List = MyListA
.Font.Name = "Times New Roman"
.Font.Size = 12
End With

End Sub

zoom38
01-17-2018, 09:58 AM
Thanks JKwan, your tweaking did the trick.

Gary

SamT
01-18-2018, 12:03 PM
Good catch on the UBounds.