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

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

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