Hi, im using this code now trying to populate my listbox without success. Brief background on what this code does:
when users make a selection in combobox2, it checks column D of worksheet "Lookup" and finds rows that match the selection. The corresponding value in column B is transferred as header to a worksheet in different columns that matches the combobox selection. It then pulls data from sheets that matches the header and populates in the rows below the header. Now after all these are populated, im trying to transfer these data into a listbox. Column number may exceed 10, that's why i can't use .Additem function. The number of rows and columns to populate in the listbox may differ based on combobox2 selection, that's why i need both rows and columns to be dynamic. Can anyone please help me with this?
sorry for all the unused variables listed as i was trying alot of different methods prior to posting
Private Sub ComboBox2_Change()
Dim ws, ws1, ws2 As Worksheet
Dim lRow, lRow2, lRow3, lCol, x, y, a, b, c, d, e As Long
Dim rng As Range
Dim i, j As Integer
Dim myarray As Variant
With Me
.ComboBox1.ListIndex = -1 = ComboBox2.ListIndex <> -1
End With
If ComboBox2.Value = "" Then
Exit Sub
End If
Me.ListBox1.Clear
With Me.ListBox1
If ComboBox2.Value <> "" Then
Set ws = Worksheets("Lookup")
Set ws1 = Worksheets(ComboBox2.Value)
ws1.Range("D:Z").Delete Shift:=xlToLeft
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 0).Row
lRow2 = ws1.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 0).Row
lCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column
For x = 2 To lRow
If ws.Cells(x, 4) = ComboBox2.Value Then
ws1.Cells(1, lCol) = ws.Cells(x, 2)
lCol = lCol + 1
End If
Next x
For Each ws2 In Sheets
For y = 4 To lCol
If ws1.Cells(1, y) = ws2.Name Then
j = 2
For i = 2 To 50
If ws2.Cells(i, 1).Value <> "" Then
ws1.Cells(j, y).Value = ws2.Cells(i, 6).Value
j = j + 1
End If
Next i
End If
Next y
Next
Set rng = (ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row & ws2.Range("A" & ws2.Columns.Count).End(xlToLeft).Column)
myarray = rng
.List = myarray
End If
End With
End Sub
Bookmarks