Multi-sheet FIND capability

[Guest]

I have a workbook with 9 sheets. Three of the sheets (Warehouse-1,
Warehouse-2, and Warehouse-3) contain thousands of part numbers. I need to
create an input box that will prompt for a specific part number, and then
search through each of the 3 aforemention sheets to find it. If found, the
cursor will jump to the appropriate cell and sheet containing the part
number. If not found, a dialog box will appear stating that the part number
could not be found.
Can someone help me write a macro to do this?
Thanks, Bob

 

[kev_06]

I have this macro setup under a command button, CommandButton1. Change
the search criteria to whatever you need. Right now, the program
searches the 1st column from row 1 to 2000 for the part number in each
of the three worksheets.

Change the 'A' to the column you would like to search in the sheets.

Let me know if you need anything more.

Private Sub CommandButton1_Click()
Dim SearchPartNum As String
Dim lngSearch As Long

Do
SearchPartNum = Application.InputBox("Enter the part number to
search for.")

If SearchPartNum = "" Then
MsgBox "Please enter a number or select cancel to exit."
ElseIf SearchPartNum = False Then
Exit Sub
End If

Loop Until Not SearchPartNum = ""

For lngSearch = 1 To 2000
If Sheets("Warehouse-1").Cells(lngSearch, 1) = SearchPartNum
Then
Sheets("Warehouse-1").Select
Worksheets("Warehouse-1").Range("A" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-2").Cells(lngSearch, 1) =
SearchPartNum Then
Sheets("Warehouse-2").Select
Worksheets("Warehouse-2").Range("A" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-3").Cells(lngSearch, 1) =
SearchPartNum Then
Sheets("Warehouse-3").Select
Worksheets("Warehouse-3").Range("A" & lngSearch).Activate
Exit Sub
End If
Next

MsgBox "The specified part number was not found."
End Sub

 

[Guest]

Thanks for your help! I sincerely appreciate it!
Bob

 

[Guest]

I tried running your macro, but I received the following error message:
Run-time error 13: Type mismatch

The line that was highlighted is:
ElseIf SearchPartNum = False Then

Can you tell me what is wrong? Thanks.
Bob

 

[kev_06]

Try this:

Note: If you still get the error, the program will still work even i
you take out the

If SearchPartNum = False Then
Exit Sub
End If

If you still have problems, let me know.



Option Explicit
Private Sub CommandButton1_Click()
Dim SearchPartNum As String
Dim lngSearch As Long

Do
SearchPartNum = Application.InputBox("Enter the part number t
search for.")

If SearchPartNum = "" Then
MsgBox "Please enter a number or select cancel to exit."
End If

Loop Until Not SearchPartNum = ""

For lngSearch = 1 To 2000
If Sheets("Warehouse-1").Cells(lngSearch, 1) = SearchPartNu
Then
Sheets("Warehouse-1").Select
Worksheets("Warehouse-1").Range("A" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-2").Cells(lngSearch, 1)
SearchPartNum Then
Sheets("Warehouse-2").Select
Worksheets("Warehouse-2").Range("A" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-3").Cells(lngSearch, 1)
SearchPartNum Then
Sheets("Warehouse-3").Select
Worksheets("Warehouse-3").Range("A" & lngSearch).Activate
Exit Sub
End If
Next

If SearchPartNum = False Then
Exit Sub
End If

MsgBox "The specified part number was not found.

 

[Guest]

I had to take out the 3 lines:

If SearchPartNum = False Then
Exit Sub
End If

in order to get the macro to run without an error message. Unfortunately,
the macro doesn't seem to find any valid part numbers that I give it. I have
re-checked the code several times and everything matches what you wrote below.

Help!

 

[kev_06]

Can you send me an example of a part number from your workbook? Also,
what column are the part numbers in and how many rows are you dealing
with (approx.)? Where are you putting this macro? Under a command
button in the "macro" section of the workbook?

 

[Guest]

Examples of part numbers: P9244.6, P9654, PA818, P9051.16, etc.
The part numbers are in column H (I modified your macro accordingly)
Number of rows <1000
I deleted the line: Private Sub CommandButton1_Click() and replaced it with
Sub SuperFind()
I then simply inserted a button from the Forms toolbar and assigned
SuperFind to it.
Hope this info helps.
Thanks again, Bob

 

[kev_06]

Try this (I forgot to mention another change previously):
Again, if you have any problems, write back.

Sub SuperFind()
Dim SearchPartNum As String
Dim lngSearch As Long

Do
SearchPartNum = Application.InputBox("Enter the part number t
search for.")

If SearchPartNum = "" Then
MsgBox "Please enter a number or select cancel to exit."
ElseIf SearchPartNum = "False" Then
Exit Sub
End If

Loop Until Not SearchPartNum = ""

For lngSearch = 1 To 1000
If Sheets("Warehouse-1").Cells(lngSearch, 8) = SearchPartNu
Then
Sheets("Warehouse-1").Select
Worksheets("Warehouse-1").Range("H" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-2").Cells(lngSearch, 8)
SearchPartNum Then
Sheets("Warehouse-2").Select
Worksheets("Warehouse-2").Range("H" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-3").Cells(lngSearch, 8)
SearchPartNum Then
Sheets("Warehouse-3").Select
Worksheets("Warehouse-3").Range("H" & lngSearch).Activate
Exit Sub
End If
Next

MsgBox "The specified part number was not found."
End Su

 

[Guest]

It works! I had to delete the lines:
ElseIf SearchPartNum = "False" Then
Exit Sub
because they still give an error message when you input an incorrect part
number.

BTW - In order to ensure that a user inputs a part number with uppercase
letters, could you kindly tell me how to incorporate Excel's UPPER function
in your code?

Thanks again for all your help. I sincerely appreciate it.
Bob

 

[kev_06]

Just add:

SearchPartNum = UCase(SearchPartNum)

below the line:

Loop Until Not SearchPartNum = ""


This will convert all text inputted through the inputbox to capital
letters.

If you need anything else, write back.

 

[Guest]

That did the trick. Thanks!
Forgive me for imposing on you one more time, but if column "H" contains
"current" part numbers, and column "I" contains "previous" part numbers, can
you tell me how to modify your code so that it will search both columns per
sheet?
Thanks again.
Bob

 

[kev_06]

Would you like 2 seperate input boxes to search the 2 different columns
or would you like 1 inputbox to search both columns on the sheets?

 

[kev_06]

Dim SearchPartNum As String
Dim lngSearch As Long

Do
SearchPartNum = Application.InputBox("Enter the part number to
search for.")

If SearchPartNum = "" Then
MsgBox "Please enter a number or select cancel to exit."
End If

Loop Until Not SearchPartNum = ""

SearchPartNum = UCase(SearchPartNum)

For lngSearch = 1 To 2000
If Sheets("Warehouse-1").Cells(lngSearch, 8) = SearchPartNum
Then
Sheets("Warehouse-1").Select
Worksheets("Warehouse-1").Range("H" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-2").Cells(lngSearch, 8) =
SearchPartNum Then
Sheets("Warehouse-2").Select
Worksheets("Warehouse-2").Range("H" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-3").Cells(lngSearch, 8) =
SearchPartNum Then
Sheets("Warehouse-3").Select
Worksheets("Warehouse-3").Range("H" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-1").Cells(lngSearch, 9) =
SearchPartNum Then
Sheets("Warehouse-1").Select
Worksheets("Warehouse-1").Range("I" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-2").Cells(lngSearch, 9) =
SearchPartNum Then
Sheets("Warehouse-2").Select
Worksheets("Warehouse-2").Range("I" & lngSearch).Activate
Exit Sub
ElseIf Sheets("Warehouse-3").Cells(lngSearch, 9) =
SearchPartNum Then
Sheets("Warehouse-3").Select
Worksheets("Warehouse-3").Range("I" & lngSearch).Activate
Exit Sub
End If
Next

If SearchPartNum = False Then
Exit Sub
End If

MsgBox "The specified part number was not found."

 

[Guest]

Just 1 inputbox to search both columns on the sheets.
Thanks!

 

[Guest]

That did the trick! Thanks a million for all your help!
Regards, Bob

 

[Guest]

Hi Kev,

(Sorry for the Access DB question in here but I was hoping you could help me)
I was trying to modify your code for Multi-sheet FIND capability in Excel,
with not much luck. I want a input box to ask the user for a purchase order
number, then search my [Invoice Items] table. These are the 3 fields I want
to search PO_#, REQ_#, TICKET_#. Then output the results to a search text
box. I want to use a command button for this.

Thanks
Richard

 

[Guest]

is there a different method, so that the macro would search all sheets so no
one needs to edit the VB for different named worksheets, or if he renames a
worksheet. this would have to work for excel 2000