Look up
 

Dim ans

On Error Resume Next
ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0)
If Not IsError(ans) Then
TextBox2.Text = Application.Index(Range("B1:B100"), ans)
TextBox3.Text = Application.Index(Range("C1:C100"), ans)
Else
MsgBox "Invalid code"
End If
On Error GoTo 0

--

HTH


RP
(remove nothere from the email address if mailing direct)


"Greg" wrote in message
...
What should I do for this?

I want to type a registration number in textbox1

and then have it look up the corresponding number and in textbox 2 & 3
show
firstname and surname.

for example

44 george smith

thanks in advance

Greg

Look up
 

set rng = Worksheets("sheet1").Range("A1:C30")
res = Application.Match(clng(Textbox1.Text),rng,0)
if not iserror(res) then
Textbox2.Text = rng(res,2).Value
Textbox3.Text = rng(res,3).value
end if

Where A1:C30 is where your list of registration numbers fall in column A,
first name in B and last name in C

--
Regards,
Tom Ogilvy


"Greg" wrote in message
...
What should I do for this?

I want to type a registration number in textbox1

and then have it look up the corresponding number and in textbox 2 & 3
show
firstname and surname.

for example

44 george smith

thanks in advance

Greg

Look up
 

My oversight - I changed methods midstream (from Vlookup to Match) and
forgot to correct my range reference to a single column.

set rng = Worksheets("sheet1").Range("A1:C30")

should be

Set rng = worksheets("Sheet1").Range("A1:A30")
res = Application.Match(clng(Textbox1.Text),rng,0)
if not iserror(res) then
Textbox2.Text = rng(res,2).Value
Textbox3.Text = rng(res,3).value
end if

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
set rng = Worksheets("sheet1").Range("A1:C30")
res = Application.Match(clng(Textbox1.Text),rng,0)
if not iserror(res) then
Textbox2.Text = rng(res,2).Value
Textbox3.Text = rng(res,3).value
end if

Where A1:C30 is where your list of registration numbers fall in column A,
first name in B and last name in C

--
Regards,
Tom Ogilvy


"Greg" wrote in message
...
What should I do for this?

I want to type a registration number in textbox1

and then have it look up the corresponding number and in textbox 2 & 3
show
firstname and surname.

for example

44 george smith

thanks in advance

Greg

Look up
 

What should I do for this?

I want to type a registration number in textbox1

and then have it look up the corresponding number and in textbox 2 & 3 show
firstname and surname.

for example

44 george smith

thanks in advance

Greg

Look up
 

Thank you Tom

Works perfect

Greg