PDA

View Full Version : Solved: search help



oleg_v
03-14-2010, 04:27 AM
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
03-14-2010, 05:16 AM
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
03-14-2010, 05:16 AM
You can do this without macros

oleg_v
03-14-2010, 05:25 AM
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
03-14-2010, 05:41 AM
try this

oleg_v
03-14-2010, 06:27 AM
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
03-14-2010, 06:37 AM
Try this code



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