Help:Find and Display

kenrose08 · #1 08-12-2006, 01:17 AM

Hi!
I have a form, 2 textbox (textbox1 and text box2), and a button.
worksheet has column A and Column B.
ex.
column A column B
Item1 1,000

i want the button to search column A with the data i've entered in textbox1 and displays the corresponding value in column B in textbox2.

#2 08-12-2006, 07:10 PM

Have a look at the Find Method of the Range Object. There's an
example in the VBA help showing how to use it.

This could be useful!:

Private Sub cmdSearch_Click()

Set searchRng = Sheets(1).Range("A:A")

With searchRng
Set resultRng = .Find(TextBox1.Text, LookIn:=xlValues)

TextBox2.Text = TextBox2.Text & Sheets(1).Range("B" &
result.Row).Value & vbCrLf

#3 08-12-2006, 07:25 PM

> TextBox2.Text = TextBox2.Text & Sheets(1).Range("B" &
> result.Row).Value & vbCrLf

spotted a typo above: result.Row should be resultRng.Row

kenrose08 · #4 08-13-2006, 12:08 AM

Thank you Sir,
this is so much help.

just another question.
what if the data i want to search is not in the list,
how do i code it?

thanks!

Quote:

Originally Posted by Tony James

> TextBox2.Text = TextBox2.Text & Sheets(1).Range("B" &
> result.Row).Value & vbCrLf

spotted a typo above: result.Row should be resultRng.Row

#5 08-13-2006, 04:15 AM

Insert the following in the If statement:

Else
MsgBox TextBox1.Text & " not found."

kenrose08 · #6 08-13-2006, 04:44 AM

thanks
but how do i start the if statement?
i really dont know how and where to code it

Welcome to the Excel Forum

Forum Statistics