Back Forum Reply New

Stop the macro if nothing found

Hi,
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      
End Sub

Hi

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
End With

'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.
Thanks again
¥
Back Forum Reply New