Listbox value return from search
 

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


--
Mcat
------------------------------------------------------------------------
Mcat's Profile: http://www.excelforum.com/member.php...o&userid=29234
View this thread: http://www.excelforum.com/showthread...hreadid=489567

Listbox value return from search
 

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 wrote:
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

Listbox value return from search
 

B-E-utiful

Thanks!


--
Mcat
------------------------------------------------------------------------
Mcat's Profile: http://www.excelforum.com/member.php...o&userid=29234
View this thread: http://www.excelforum.com/showthread...hreadid=489567

Listbox value return from search
 

You're welcome.

Mcat wrote:
B-E-utiful

Thanks!