Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up
Thank you Tom
Works perfect Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|