Counting the number of formulas in a spreadsheet

[mapalumbo]

I have been tasked with counting the total number of formulas in massive spreadsheets for Sarbanes Oxley purposes.

I have used tools, options, view tab then clicked on formulas. Then I went to each individual sheet and highlighted anything that was not text or a number and used the count function...

Any help, tools or thoughts would be appreciated.

ray:

 

[Smitty]

Welcome to the Board!

I believe that there is a Formula Count function in an add-in or old post, but while I try to dig that up see if this helps any:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> CountFormulas()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
        x = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Count
       MsgBox x

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It can be modified to write to a new sheet and give a total of formulas in each sheet if you want.

Smitty

 

[mapalumbo]

Thanks Smitty!

Please add this to your permanent record. It works perfectly!!!!!

Mike Palumbo
mapalumbo@yahoo.com

 

[Tazguy37]

Or if you want to go through alll the sheets:

Sub test7()
Dim Sht As worksheet

    For Each Sht In Worksheets
        On Error Resume Next
        MsgBox ("Sheet " & Sht.Name & " has " & Sht.Cells.SpecialCells(xlCellTypeFormulas).Count) & " formulas."
    Next Sht

End Sub

Hope that helps!