List Box Match

[Hazel]

Hi All

I have adapted the code below from something I found in this forum

Private Sub Cmd1_Click()
Dim rngToSearch As Range
Dim rngFound As Range
Set rngToSearch = ActiveSheet.Columns("F")
Set rngFound = rngToSearch.Find(What:=Tb1.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb1.Value & " was not found."
Else
rngFound.Select

End If

End Sub

It works OK and finds the number I'm searching for in Column "F" what I
would like to happen is that on finding the correct selection it will also
highlight the row in the list box "Lb1" which also contains the same number
in Column 5 in the list box ,it would then display the information in a new
TextBox "Tb2" -- I would then not have to keep closing the UserForm to read
all the info. Hope I have expained it all properly.

 

[Tom Ogilvy]

Maybe something like this:

Private Sub Cmd1_Click()
Dim rngToSearch As Range
Dim rngFound As Range
Set rngToSearch = ActiveSheet.Columns("F")
Set rngFound = rngToSearch.Find(What:=Tb1.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb1.Value & " was not found."
Else
With Lb1
for i = 0 to .listcount - 1
if .list(i,4) = tb1.value then
lb1.ListIndex = i
for each cell in Range(cells(rngFound.row,1), _
cells(rngFound,row,6)) '<== change the 6 to reflect number of
columns
s = cell.Value & ","
Next
TB2.value = Left(s,len(s)-1)
exit for
end if
Next
rngFound.Select
End With
End If


End Sub

 

[Hazel]

Hi Tom

Thank you for the response however the following error is cropping up

Compile Error.

Wrong number of arguments or invalid property assignment

Have commented out where the error crops up does it matter that I'm using
RowSource to fill "Lb1"

 

[Tom Ogilvy]

cells(rngFound,row,6)) has a typo

cells(rngFound.row,6))

The code was a suggested approach. It wasn't intended to be a paste in
solution. It hasn't been tested.

 

[Hazel]

Hi Tom

I did paste it and I tend not to alter anything until I can get it working
and comma's and full stops are all part of Excel and I would never know if
one was right or wrong. Anyway got it working and doing just what I want -
you guys amaze me sometimes with your knowledge how do you remember it all ???