LMPhillips
asked on
Want source file name to be listed in results of Excel macro
I have just used the code provided in ID24183402. It worked very well and I would like to be able to add the source file name to be listed in the results. Thanks!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER