Solved: search help

[oleg_v]

hi
i need help with a macro:
i attached a file.

in sheet 1 i have a data table in sheet2 i have a result table
i need a macro with a push button to search in the "serial" column in sheet1
and the data in the sheet2 will belong to the serial


thanks

[Bob Phillips]

Use a formula

=INDEX(Sheet1!$I$7:$I$35,MATCH($B1,INDEX(Sheet1!$A$7:$H$35,0,MATCH(Sheet2!$ A1,Sheet1!$A$3:$H$3,0)),0))

[mdmackillop]

You can do this without macros

[oleg_v]

hi
thanks for the reply
but like i said i need a macro with a button
because i need to explain to my emplyes i need them to press the button write a serial number and get instent results
so please help with a macro it is critical for the production


thanks

[mdmackillop]

try this

[oleg_v]

hi thanx

this is approximatly what i need but i changed the combobox1 to textbox1
i just need that if the number that i write is not in the column
write msgbox "no mach found"

thanks

[Bob Phillips]

Try this code

[vba]

Private Sub CommandButton1_Click()
Dim c As Range

Set c = Sheets("Sheet1").Columns(9).Find( _
What:=TextBox1.Text, _
lookat:=xlWhole)
If c Is Nothing Then

MsgBox "Value not found, try again"
Else

c.Offset(, -8).Resize(, 8).Copy

Sheets("Sheet2").Cells(1, 2).PasteSpecial Paste:=xlValues, Transpose:=True

Unload UserForm1
Application.CutCopyMode = False

Worksheets("Sheet2").Activate
End If
End Sub
[/vba]