Inserting single item into ListBox from range

Question

I have the following code that from ListBox1 populates ListBox2. The boxes are populated from a worksheet that has header of names (A:AC) and underneath each name is a list of Ids that will populate into ListBox2. There could be multiple IDs for each person or some just have one. My 2 questions are 1. How can I insert into listbox from a dynamic column 2. During testing if there is more than one ID it inserts great but if only one ID error.

Private Sub ListBox1_Change()

Dim myArray As Variant
'pulls selected value from listbox1
myArray = ListBox1.List(ListBox1.ListIndex, 0)

Worksheets("Sheet1").Select
Columns("A:CC").Select
'looks for selected value from listbox1
Set found = Cells.Find(What:=myArray, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)


If found Is Nothing Then
    'skipper
Else
    found.Offset(1, 0).Select
End If

'below is where i tried to assign the column letter to a variable, didn't work
'Col = Split(ActiveCell(1).Address(1, 0), "$")(0)
'Me.ListBox2.List = Worksheets("Sheet1").Range(Col & "2:" & Col & Range(Col & Range(Col & Rows.Count).End(xlUp).Row).Value

'belwo works great if more than one ID, otherwise it's crap
Me.ListBox2.List = Worksheets("Sheet1").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Value

End Sub

Some progress. I've got it to find and populate via the dynamic column but still won't work if the range is just a single cell. Thanks to avb for the bit of code.

Else
   Set test = Range(found.Offset(1, 0), found.End(xlDown))
test.Copy
End If

Me.ListBox2.List = test.Value

SOLUTION! What I found out is that if there is only one item selected you need to use the .AddItem as opposed to the .List I was using. I ended up with this final snippet.

Set sId = Range(found.Offset(1, 0), found.End(xlDown))
    sId.Copy

If IsEmpty(found.Offset(2, 0).Value) Then
   With Worksheets("Sheet1")
      Me.ListBox2.AddItem sId
   End With
Else
   Me.ListBox2.List = sId.Value
End If

mrbungle mrbungle · Accepted Answer · 2014-05-30T21:19:40

SOLUTION! What I found out is that if there is only one item selected you need to use the .AddItem as opposed to the .List I was using. I ended up with this final snippet.

Set sId = Range(found.Offset(1, 0), found.End(xlDown))
    sId.Copy

If IsEmpty(found.Offset(2, 0).Value) Then
    With Worksheets("Sheet1")
    Me.ListBox2.AddItem sId
End With
Else
    Me.ListBox2.List = sId.Value
End If

Inserting single item into ListBox from range

2 Answers