Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Bob Bob is offline
external usenet poster
 
Posts: 972
Default Multi-sheet FIND capability

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Multi-sheet FIND capability


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   Report Post  
Posted to microsoft.public.excel.programming
Bob Bob is offline
external usenet poster
 
Posts: 972
Default Multi-sheet FIND capability

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find a value with multi-column, multi-record list Dallasm Excel Worksheet Functions 1 May 30th 10 05:40 PM
Why does a Multi-sheet/user file open on sheet two every time? Frustrated in NJ Excel Discussion (Misc queries) 1 January 22nd 10 05:46 PM
find in multi amr Excel Worksheet Functions 1 May 26th 09 07:41 AM
Find and copy across multi-sheet work book chris Excel Programming 1 June 30th 05 07:28 PM
Pivot table Format Style Sheet Capability Dave Shafer Excel Discussion (Misc queries) 1 January 29th 05 08:27 PM


All times are GMT +1. The time now is 05:06 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"