Macro to Find All text or values in the workbook


I am looking for a macro that would search for a particular text string (preferably typed into an input box) and search the entire workbook. Once the value(s) is/are found, I'd like one of two things: Either to be able to go through all the found values to select one manually, or to have the list of all found values displayed similar to the Find All option in the Find dialog box.



If my answer works for you don't forget to "Select" it as the answer. There is a button at the bottom of the post that allows you to do that.
don (rep: 1247) Jul 5, '16 at 4:29 pm
Add to Discussion


Selected Answer

Try this macro:

Sub find_value()

strSearch = InputBox("Type your search term below.")

Dim SHT As Worksheet
Dim rFND As Range
Dim sFirstAddress

For Each SHT In ThisWorkbook.Worksheets
 Set rFND = Nothing
 With SHT.UsedRange
  Set rFND = .Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
  If Not rFND Is Nothing Then
   sFirstAddress = rFND.Address

    msg_response = MsgBox("Do you want to use this value?", vbYesNo)

    If msg_response = 6 Then GoTo OutsideLoop

    Set rFND = .FindNext(rFND)
   Loop While Not rFND Is Nothing And rFND.Address <> sFirstAddress
  End If
 End With

If IsEmpty(sFirstAddress) Then MsgBox "Value not found."

'Do something here once the user chooses a value

End Sub

I modified this from something I found on the web. I assumed that you wanted this as a part of a larger macro so I set the code to go to the end of this current macro once the user decided they wanted to use a specific match.



Thanks! This does work well enough to get me going. You are right - this is part of a bigger process. It is hard to tell which value is being selected, so I might try to modify this macro to highlight the row or the value, but other than that, this is perfect. Thank you very much!!
mpoatsy Jul 5, '16 at 3:51 pm
The current macro does navigate to the worksheet of the currently found value and selects the cell that contains it but you could also put the cell location info in the message box if you wanted or highlight it with another color like you mention. Lot's of possibilities once you get the difficult stuff finished.
don (rep: 1247) Jul 5, '16 at 4:29 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login