MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
Sub FindAll() Dim wks As Worksheet, wksOut As Worksheet Dim colFoundRanges As Collection Dim strFind As String Dim lngIndex As Long On Error Resume Next Application.DisplayAlerts = False Sheets("Find results").Delete Application.DisplayAlerts = True Set wksOut = Worksheets.Add wksOut.Name = "Find results" Set colFoundRanges = New Collection strFind = InputBox("Enter string to find") For Each wks In ActiveWorkbook.Worksheets FindCells wks, strFind, colFoundRanges Next wks With colFoundRanges For lngIndex = 1 To .Count With .Item(lngIndex) wksOut.Cells(lngIndex, 1).Value = .Parent.Name wksOut.Cells(lngIndex, 2).Value = .Address wksOut.Cells(lngIndex, 3).Value = "'" & .Formula End With Next lngIndex End With End Sub Sub FindCells(ws As Worksheet, strToFind As String, colOutput As Collection) Dim strFirstAddress As String Dim rngFound As Range With ws.UsedRange Set rngFound = .Find(what:=strToFind, lookat:=xlPart, LookIn:=xlFormulas, MatchCase:=False) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do colOutput.Add rngFound, ws.Name & "-" & rngFound.Address Set rngFound = .FindNext(rngFound) Loop While rngFound.Address <> strFirstAddress End If End With End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.