End "Application.InputBox" loop

[Jasonx]

Hello,

I have an input box that searches an entire workbook for a specified word. The problem I am running into is when it finds the word, I am unable to stop the loop from locating other occurances of the word in the workbook. I would like to stop this at any time without it running it's entire course.

I would also like this to search only A-F columns of each sheet.

Thanks,

Sub Keyword()
Dim oSheet As Object
Dim Firstcell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
WhatToFind = Application.InputBox("What are you looking for ?", "Search", , 100, 100, , , 2)
If WhatToFind = False Then
Exit Sub
End If
If WhatToFind <> "" And Not WhatToFind = False Then
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Activate
oSheet.[a1].Activate
Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Firstcell Is Nothing Then
Firstcell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34))
On Error Resume Next
While (Not NextCell Is Nothing) And (Not NextCell.Address = Firstcell.Address)
Set NextCell = Cells.FindNext(After:=ActiveCell)
If Not NextCell.Address = Firstcell.Address Then
NextCell.Activate
MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34))
End If
Wend
End If
Set NextCell = Nothing
Set Firstcell = Nothing
Next oSheet
End If
End Sub

 

[VoG]

You may be able to adapt this

Sub Ffind()
Dim Found As Range, tempcell As Range, Response As Integer, X
X = InputBox("Please enter what you want to find")
Set Found = Cells.Find(What:=X)
If Found Is Nothing Then
    MsgBox X & " Not Found"
    Exit Sub
Else
    Application.Goto reference:=Found, scroll:=True
End If
Response = MsgBox("Find again", vbYesNo)
If Response = vbYes Then
    Do
        Set tempcell = Cells.FindNext(After:=Found)
        If Found.Row >= tempcell.Row And Found.Column >= tempcell.Column Then
            MsgBox "Not found again"
            Exit Do
        End If
        Set Found = tempcell
        Application.Goto reference:=Found, scroll:=True
        Response = MsgBox("Find again", vbYesNo)
        If Response = vbNo Then Exit Do
    Loop
End If
End Sub

 

[jbeaucaire]

Add an opportunity to bail out into the msgbox:

    If MsgBox("Found " & Chr(34) & WhatToFind & Chr(34) & vbLf & _
        "Do you want to continue searching?", _
        vbYesNo + vbQuestion) = vbNo Then Exit Sub

 

[Marchut]

Sub Keyword()
    Dim searchRange As Range
    Dim askStop As Boolean
    Dim WhatToFind As Variant
    WhatToFind = Application.InputBox("What are you looking for ?", "Search", , 100, 100, , , 2)
    If WhatToFind = False Then Exit Sub
    If WhatToFind <> "" And Not WhatToFind = False Then
        Set searchRange = Range(Cells(1, 1), Cells(Range("A:F").End(xlDown).Row, 6))
    
        For I = 1 To searchRange.Count
            If searchRange(I) = WhatToFind Then
                If (MsgBox(WhatToFind & " was found in Cell " & searchRange(I).Row & "," & searchRange(I).Column & ". Would you like to stop?", vbYesNo)) Then Exit For
            End If
        Next I
    End If
End Sub