Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() 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 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to find a value with multi-column, multi-record list | Excel Worksheet Functions | |||
Why does a Multi-sheet/user file open on sheet two every time? | Excel Discussion (Misc queries) | |||
find in multi | Excel Worksheet Functions | |||
Find and copy across multi-sheet work book | Excel Programming | |||
Pivot table Format Style Sheet Capability | Excel Discussion (Misc queries) |