Needle in a haystack...searching for VBA code in all Excel sheets
Let's say we have a group of workbooks in a folder and we suspect that on
some sheet of some workbook the function =SUM() is being used. We want to
find the workbook, the sheet, and the cell holding the function:
1. open the workbooks in the folder
2. run the following:
Sub findit()
For Each wb In Workbooks
wb.Activate
For Each sh In wb.Sheets
sh.Activate
For Each r In sh.UsedRange
v = r.Formula
If InStr(v, "SUM(") 0 Then
r.Select
Exit Sub
End If
Next
Next
Next
End Sub
Clearly this code can be modified to open each workbook in series.
--
Gary''s Student - gsnu2007i
"CodeMonkey" wrote:
I can't think of a way but I hope there is one where I can search a
directory of Excel sheets for a function call. Or ones that use
certain xla files.
Otherwise, I have to pull out my hair.
THANKS
John
|