[MASANOVIC]

Can anyone help. Im trying to search a sheet and display results on a form using labels in excel 2000. Ive nearly done it!!! but am gettin an error. Can anyone please see if they can get it workin. The code is:

Private Sub CommandButton1_Click()

Dim fnd As Range ' this will be the samew as your entry in TextBox1
Dim tbl As Range ' this will be the table to search

Set tbl = Sheet1.Range("A2"). CurrentRegion

'run the search
Set fnd = tbl.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
'if the item is not found the user is informed and the textbox cleared
If fnd Is Nothing Then
MsgBox "No match found!"
TextBox1.Value = ""

Exit Sub
'if the item is found the details will be copied to the labels
Else: fnd.Activate
End If

'now put the information stored adjacent to the found item into label1,etc
Label1.Caption = fnd.Offset(0, 1).Value
Label2.Caption = fnd.Offset(0, 2).Value 'and so on
End Sub

[savbill]

2 things I see that may be causing a problem. I havn't ran this to check but suspect the way the range is defined and not using the reference to the Userform would cause problems. Although because this is in the Form module it does not require an explicit reference to the form. you can refer to the form as "Me" when used in the Form Module.

Here's a quick edit to try

Private Sub CommandButton1_Click() 

Dim fnd As Range ' this will be the samew as your entry in TextBox1
Dim tbl As Range ' this will be the table to search

Set tbl = Sheet1.Range("A2:A50") ' or whatever size your range is
'run the search
Set fnd = tbl.Find(What:=TextBox1.Value, After:=Sheet1.Range("A2"), LookIn:=xlValues, LookAt:= _ 
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ 
, SearchFormat:=False) 
'if the item is not found the user is informed and the textbox cleared
If fnd Is Nothing Then 
''' MsgBox "No match found!"  ''' Or skip this and put it in the TextBox
Me.TextBox1.Value = "No match found!" 
Exit Sub 
'if the item is found the details will be copied to the labels
Else
 fnd.Activate ' use 2 lines vrs the ":" colon after Else
End If 

'now put the information stored adjacent to the found item into label1,etc
Me.Label1.Caption = fnd.Offset(0, 1).Value 
Me.Label2.Caption = fnd.Offset(0, 2).Value 'and so on
End Sub