VBA UserForm Question.......Help!

[Sam Torasco]

Hi I was wondering if someone could please help me.....
On my UserForm i have 4 textboxes, i want to code it so when
TextBox1_Change() it will take the data entered into it ie(1234567)
searh a work sheet for the data entered and when found will return the
result of the cell in the next collum where the 1234567 was found IN
TextBox2 and if not found update TextBox2 with "Not Found"

Any help would be appriciated,
Thanks
Sam

 

[Vasant Nanavati]

Something like:

Private Sub TextBox1_Change()
Dim rng As Range
On Error Resume Next
Set rng = Cells.Find(TextBox1, , , xlWhole)
If rng Is Nothing Then
TextBox2 = "Not Found"
Else
TextBox2 = rng.Offset(, 1)
End If
End Sub

 

[David C.]

Any help would be appriciated,

Well I assume that the point is "looking for the data" and not changing
Textbox2 value or detecting the _change event on box1

I think there many solutions

First:
using the Find method (see in the VBE help)
this does the same than the "CTRL+F" find manual method
You can check each Sheet by

For each Sh in Sheets
.....
next


Second:
using only loops, not necessary for you, but sometimes permit more complex
activity:
for each sh in sheets
for each cell in sh.cells
...
next
next


I hope this will help enought.

 

[Tom Ogilvy]

Dim rng as Range
set rng = Cells.find(Textbox1.Value)
if not rng is nothing then
Textbox2.Text = rng.offset(0,1).Value
else
Textbox2.Text = "Not Found"
End if

see help on the find method for other arguments you may use to control the
search.

 

[Sam Torasco]

Thanks,
Im on the right track now. but how do i tell it to search the data on
sheet2?

Sam

 

[Sam Torasco]

Thanks,
Im on the right track now. but how do i tell it to search the data on
sheet2?

Sam

 

[Vasant Nanavati]

Replace:

Cells.Find

with:

Sheet2.Cells.Find