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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Macro to Find All text or values in the workbook
0
Discussion
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: 1989) Jul 5, '16 at 4:29 pm
Answers
0
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
Do
SHT.Activate
rFND.Select
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
Next
If IsEmpty(sFirstAddress) Then MsgBox "Value not found."
OutsideLoop:
'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.
Discussion
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: 1989) Jul 5, '16 at 4:29 pm