Search function for a workbook
 

First, thanks for all the great help!

Here is my situation:

I have an inventory workbook that I want to add a search
page to. I would like to actually have 2 separate search
boxes, one to search by part number and the second to
search by description.

Hope this is an easy one.

Thanks,
Brian

Search function for a workbook
 

please provide more detail. what do you want returned, if it exist or where its located? what format is your data in?. how is your workbook setu


----- brian wrote: ----

First, thanks for all the great help

Here is my situation

I have an inventory workbook that I want to add a search
page to. I would like to actually have 2 separate search
boxes, one to search by part number and the second to
search by description

Hope this is an easy one

Thanks
Bria

Search function for a workbook
 

Sorry, about that.

Here is more detail.

I would like it to jump to the location if the part number
is found.

I don't know if this will work for the description as some
description can be similiar, any suggestions for this
part, maybe just the worksheet/cell locations of matching
results.

Part numbers are mainly numeric but can be alpha-numeric.

Descriptions are text only.

Hope this helps.

-----Original Message-----
please provide more detail. what do you want returned, if
it exist or where its located? what format is your data
in?. how is your workbook setup


----- brian wrote: -----

First, thanks for all the great help!

Here is my situation:

I have an inventory workbook that I want to add a
search
page to. I would like to actually have 2 separate
search
boxes, one to search by part number and the second
to
search by description.

Hope this is an easy one.

Thanks,
Brian

.

Search function for a workbook
 

This is still pretty general in terms of detail. are there specific columns or sheets with part numbers vs. descriptions?..that kind of stuff. So, you will get a generic Search function

Create a textbox and a command button on a new shee
Name The New sheet 'SearchSheet" or whaterver you want just adjust code to match
in the Click Code for the command button put thi

Private Sub CommandButton1_Click(
Dim x as Range, Sh as Rang
For Each Sh In Sheet
If Sh.Name = "SearchSheet" then Goto NextShee
Set x = Sh.Cells.Find(What:=TextBox1.text, LookAt:=xlWhole, MatchCase:=False
If Not x Is Nothing The
Sh.Selec
x.Selec
Exit Su
End I
NextSheet
Next S
End Sub
----- brian wrote: ----

Sorry, about that

Here is more detail

I would like it to jump to the location if the part number
is found.

I don't know if this will work for the description as some
description can be similiar, any suggestions for this
part, maybe just the worksheet/cell locations of matching
results

Part numbers are mainly numeric but can be alpha-numeric

Descriptions are text only

Hope this helps

-----Original Message----
please provide more detail. what do you want returned, if
it exist or where its located? what format is your data
in?. how is your workbook setu
----- brian wrote: ----
First, thanks for all the great help
Here is my situation
I have an inventory workbook that I want to add a
search
page to. I would like to actually have 2 separate
search
boxes, one to search by part number and the second
to
search by description
Hope this is an easy one
Thanks
Bria

Search function for a workbook
 

Here is some cool vba that will do what you are looking
for.


Public SearchCriteria, FirstCell As Variant
Sub findbutton_click()

'If the search criteria does not occur in the worksheet,
an error will occur.
On Error GoTo Error_Handler

'Start in cell A1
ThisWorkbook.Activate
Range("A1").Select

'Get search criteria and assign it to the variable
SearchCriteria
SearchCriteria = InputBox("Enter Store Number", "Search
Criteria")

'Perform search using SearchCriteria
Cells.Find(What:=SearchCriteria, After:=ActiveCell,
LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False).Select

'Assign address of first found cell to FirstCell
FirstCell = ActiveCell.Address

'Check to see if this is the desired row
FindNext:
If FindNextCell = FirstCell Then
MsgBox "You have searched the entire worksheet.",
vbOKCancel, "End of Search"
Range("A1").Activate
Exit Sub
End If
Response = MsgBox("Is this the correct cell?",
vbYesNoCancel, "Find Next")

If Response = vbNo Then
Cells.FindNext(After:=ActiveCell).Activate
FindNextCell = ActiveCell.Address
GoTo FindNext
End If

Exit Sub

Error_Handler:
MsgBox "The search criteria you entered does not occur in
the worksheet.", vbOKCancel, _
"Search Criteria Error"

End Sub


I love this one. I did this for users that had difficulty
with ctrl-f. Go figure.
Good Luck.







-----Original Message-----
Sorry, about that.

Here is more detail.

I would like it to jump to the location if the part
number
is found.

I don't know if this will work for the description as
some
description can be similiar, any suggestions for this
part, maybe just the worksheet/cell locations of matching
results.

Part numbers are mainly numeric but can be alpha-numeric.

Descriptions are text only.

Hope this helps.

-----Original Message-----
please provide more detail. what do you want returned,
if
it exist or where its located? what format is your data
in?. how is your workbook setup


----- brian wrote: -----

First, thanks for all the great help!

Here is my situation:

I have an inventory workbook that I want to add a
search
page to. I would like to actually have 2 separate
search
boxes, one to search by part number and the second
to
search by description.

Hope this is an easy one.

Thanks,
Brian

.

.

Search function for a workbook
 

Thanks for the info.

I am too much of a novice to know how to implement what
you have suggested :(

I can get the text box and the command button in place,
but I can't seem to type anything in to the text box to
search. I get an error message, "Reference invalid".

Search function for a workbook
 

Brian,

Start like this.

1)Tools\Macro\RecordMacro
2)StopMacro
3)Alt-F11
4)While in vb editor look for project explorer to the
right. If not there type ctrl-r. Select the worksheet you
are working on and open modules. There will only be one as
it was created using the recordmacro.
5)Copy and paste the vb. Change the msg text as needed.

Play around with this until you get the hang of it.

Hope this helps.


-----Original Message-----
Thanks for the info.

I am too much of a novice to know how to implement what
you have suggested :(

I can get the text box and the command button in place,
but I can't seem to type anything in to the text box to
search. I get an error message, "Reference invalid".

.

Search function for a workbook
 

Your still in design mode! but while your there Put the code in the Commandbuttons Code by right clicking it View code and pasting the code: Make sure your textbox name follows What:=, use right click View code to get name , minus "_Change". Also i changed xlWhole to Xlpart in case you want to find a piece of text, change it if you want.

to get out of design mode do this: View Toolbars put check on Control Toolbar: Click the blue triangle

Dim x as Range, Sh as Range
For Each Sh In Sheets
If Sh.Name = "SearchSheet" then Goto NextSheet
Set x = Sh.Cells.Find(What:=TextBox1.text, LookAt:=xlPart, MatchCase:=False)
If Not x Is Nothing Then
Sh.Select
x.Select
Exit Sub
End If
NextSheet:
Next Sh
End Sub
----- brian wrote: -----

Thanks for the info.

I am too much of a novice to know how to implement what
you have suggested :(

I can get the text box and the command button in place,
but I can't seem to type anything in to the text box to
search. I get an error message, "Reference invalid".