I am writing a program that counts how many times each range names in a
workbook is used in the formulae. At the moment I am looping through
each name and then looping through each sheet and performing the find
method on the cells.specialcells(xlcelltypeformulas). My problem is that
with 600 range names and 30 sheets, the process takes at least half an
hour to complete.

I am wondering if there is a better alternative to the find method that
would be quicker. I am considering setting up arrays to store all the
formulae on each sheet and to perform a search in these arrays, but
because the formulas or not contiguous the arrays themselves are not
easy to set up and so the benefit might not be there. I have also
played around with looping through the sheets first and then the names
afterwards, but this approach actually appears slower.

I am using xl2000, and the code used for finding is as follows:

Set rSearch = sh.Cells.SpecialCells(xlCellTypeFormulas)
Set rFound = rSearch.Find(What:=strFind, LookIn:=xlFormulas,
lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext)

' If a match is found then
If Not rFound Is Nothing Then
' Record a record of the first cell address found (traps when the
search repeats)
strFirstAddress = rFound.Address

' Perform a search on the sheet for further references.
Do
' Count the occurrences
lCountNames = lCountNames + 1
' Find the next match
Set rFound = rSearch.FindNext(rFound)
' Loop only if we haven't already found all of the references.
Loop While Not rFound Is Nothing And rFound.Address <
strFirstAddress

Any advice would be greatly received.

Many thanks in advance,
Simon Livings

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!