Listbox value return from search

[Mcat]

I am trying to create a UserForm that will search a spreadsheet and
return multiple values to a listbox. For example the program below
searchs column E for the Value in textbox1.

How do I then return the value from column A of the same row to
listbox1?
Thanks,
M

Private Sub CommandButton7_Click()
With Worksheets(1).Range("e:e")
Set e = .Find(TextBox1, LookIn:=xlValues)
If Not e Is Nothing Then
firstAddress = e.Address
Do
"Write value from column A in same row to ListBox1"
Set e = .FindNext(e)
Loop While Not e Is Nothing And e.Address <> firstAddress
End If
End With
End Sub

 

[Rowan Drummond]

Assuming CommandButton7 is on the same userform as ListBox1 and TextBox1
try:

Private Sub CommandButton7_Click()
Dim e As Range
Dim firstAddress As String
With Worksheets(1).Range("e:e")
Set e = .Find(Me.TextBox1.Value, LookIn:=xlValues)
If Not e Is Nothing Then
firstAddress = e.Address
Do
Me.ListBox1.AddItem e.Offset(0, -4).Value
Set e = .FindNext(e)
Loop While Not e Is Nothing And e.Address <> firstAddress
End If
End With
End Sub

Hope this helps
Rowan

 

[Mcat]

B-E-utiful

Thanks!

 

[Rowan Drummond]

You're welcome.