Consulting

Results 1 to 7 of 7

Thread: Add Unequal Amount of Data To Two Columns of UserForm List Box

  1. #1
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    323
    Location

    Add Unequal Amount of Data To Two Columns of UserForm List Box

    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
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    323
    Location
    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

  4. #4
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    323
    Location
    Sam i've been working with your code and can get passed the error above but I can't get both columns to populate.

  5. #5
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    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

  6. #6
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    323
    Location
    Thanks JKwan, your tweaking did the trick.

    Gary

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Good catch on the UBounds.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •