Find the value by TextBox..

[asyamonique]

Dim ws As Worksheet, rngFind As Range
Set ws = ThisWorkbook.Sheets("staff")
Set rngFind = ws.Range("i:i").Find(what:=Me.TextBox2.Value, MatchCase:=True)
If Not rngFind Is Nothing Then
Me.TextBox20.Value = rngFind.Offset(0, 1).Value
Me.TextBox21.Value = rngFind.Offset(0, 2).Value
Else
Me.TextBox20.Value = "Not Found!"
TextBox21 = ("")
End If

Could someone help me to improve that given code above?
Its simply find column B value by entering the column A value into the TextBox2.Everything fine with that if the value is single like a name or ID number.....
My question is how can i make it more functional , like if the name is with 3 names and the user want to seach by second name or 3th?
If the name on column B ; Roberto Carlos Gonzales and if i type the Carlos name on TextBox2 then whole name will be find.
Hope its clear!
Many Thanks.

 

[Domenic]

Try replacing...

Set rngFind = ws.Range("i:i").Find(what:=Me.TextBox2.Value, MatchCase:=True)

with

Set rngFind = ws.Range("i:i").Find(what:=Me.TextBox2.Value, [COLOR="Red"]LookAt:=xlPart[/COLOR], MatchCase:=True)

 

[asyamonique]

Thanks for your reply but its not working ...
Some of the names are not in order , like (Mary,Jane.. Basco.)
Is that one could be the reason?
Thanks

 

[Domenic]

I've just re-read your original post, and it's a little confusing. If Column A contains names, such as "Roberto Carlos Gonzales", and the TextBox contains a value for which to search, such as "Carlos", try...

= ws.Range("[COLOR="Red"]a:a[/COLOR]").Find(what:=Me.TextBox2.Value, LookAt:=xlPart, MatchCase:=True)

Also, note that MatchCase is set to True, so Find is case-sensitive. If you don't want the search to be case-sensitive, set it to False.

 

[asyamonique]

I've just re-read your original post, and it's a little confusing. If Column A contains names, such as "Roberto Carlos Gonzales", and the TextBox contains a value for which to search, such as "Carlos", try...

= ws.Range("[COLOR=red]a:a[/COLOR]").Find(what:=Me.TextBox2.Value, LookAt:=xlPart, MatchCase:=True)

Also, note that MatchCase is set to True, so Find is case-sensitive. If you don't want the search to be case-sensitive, set it to False.

Thanks Domenic,