I'm using the following code to search about 1400 sheets for a particular part number. When all occurences of that number have been found and pasted inot the search results sheet, then the next 3 macros run in order to do lots of other things which are irrelevant to the question. If the search finds no results, I want the macro to stop and display something like Part Number not found, and ignore the other 3 macros.
Thanks for looking, any help greatly appreciated.
Here's the code I'm using
Sub FindAllSheets() Dim Found As Range, WS As Worksheet, LookFor As Variant LookFor = InputBox("Enter value to find")If LookFor = "" Then Exit Sub' Clear or Add a Results sheetIf SheetExists("Search Results") Then Sheets("Search Results").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("H1").Select Selection.ClearContentsElse Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = "Search Results"End IfFor Each WS In ActiveWorkbook.Worksheets If WS.Name "Search Results" Then Set Found = WS.Cells.Find(What:=LookFor) If Found Is Nothing Then Range("D5").Select Else Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1) Found.EntireRow.Interior.Color = vbYellow End If End IfNext WSSheets("Search Results").ActivateColumns("A:G").SelectSelection.Columns.AutoFitRange("B2").Select Call ADMIN_HYPERS Call Replace Call FIND_DOCUMENTS
A simplistic addition to your macro would be to check what the last row containing a value is on the results sheet, and if it is anything other than row 1 then you have some data returned (do this after the Next WS line). You could use something like:Code:
With Sheets("Search Results") Set rngLast = .Cells.Find(What:="*",After:=.Cells(1,1),Lookin:=xlValues,SearchDirection:=xlPrevious,SearchOrder:=xlByRows) If rngLast Is Nothing Then MsgBox "Search item not found! Exiting Sub...":Exit Sub Else If rngLast.Row = 1 Then _ MsgBox "Search item not found! Exiting Sub...":Exit Sub End If
'rest of macroOf course, an even better idea would be to maintain a counting variable that you increment every time something is found - then after the loop check if it has a value >0 and base your following actions on this...
Thank you very much Richard, the first peice of code makes it do exactly what I wanted. The second answer was so far over my head I couldn't even see it.