Needle in a haystack...searching for VBA code in all Excel sheets orfinding xls users of xla sheets.

[CodeMonkey]

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

 

[Gary''s Student]

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.