Multi-sheet FIND capability

[Bob]

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

[Bob]

Thanks for your help! I sincerely appreciate it!
Bob


"kev_06" wrote:

>
> 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
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=570599
>
>

[Bob]

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" wrote:

>
> 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
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=570599
>
>

[kev_06]

Try this:

Note: If you still get the error, the program will still work even if 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 to 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) = 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

If SearchPartNum = False Then
Exit Sub
End If

MsgBox "The specified part number was not found."

[Bob]

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" wrote:

>
> Try this:
>
> Note: If you still get the error, the program will still work even if
> 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 to
> 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) = 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
>
> If SearchPartNum = False Then
> Exit Sub
> End If
>
> MsgBox "The specified part number was not found."
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=570599
>
>

[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?

[Bob]

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" wrote:

>
> 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?
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=570599
>
>

[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 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 1000
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
End If
Next

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

[Bob]

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" wrote:

>
> 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 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 1000
> 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
> End If
> Next
>
> MsgBox "The specified part number was not found."
> End Sub
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=570599
>
>

[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.

[Bob]

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" wrote:

>
> 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.
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=570599
>
>

[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."

[Bob]

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

"kev_06" wrote:

>
> 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
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=570599
>
>

[Bob]

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

"kev_06" wrote:

>
> 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."
>
>
> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=570599
>
>