How many formula

A

Andy the yeti

Hi all,

Is there a way to count up how many formulae there are in workbook across
all the tabs?

Many thanks

Andy
 
P

Peo Sjoblom

For a single sheet yes, press F5, select special then formulas, now right
click in the status bar to the far right and select count. Otherwise you
need to use a macro

******************************************************
Option Explicit

Sub CountFormula()

Dim sh As Worksheet
Dim cell As Range
Dim Count As Integer

For Each sh In ActiveWorkbook.Sheets
For Each cell In sh.UsedRange
If cell.HasFormula Then
Count = Count + 1
End If
Next cell
Next sh

MsgBox "This workbook has " & Count & " formulas"

End Sub

*****************************************************
 
C

Chip Pearson

Only with VBA code.

Sub CountFormulas()
Dim TotalCount As Long
Dim WSCount As Long
Dim WS As Worksheet
On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
WSCount = WS.UsedRange.SpecialCells(xlCellTypeFormulas).Count
TotalCount = TotalCount + WSCount
Next WS
MsgBox "You have " & Format(TotalCount, "#,##0") & " formulas."
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
news:[email protected]...
 
A

Andy the yeti

thank you both !

Chip Pearson said:
Only with VBA code.

Sub CountFormulas()
Dim TotalCount As Long
Dim WSCount As Long
Dim WS As Worksheet
On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
WSCount = WS.UsedRange.SpecialCells(xlCellTypeFormulas).Count
TotalCount = TotalCount + WSCount
Next WS
MsgBox "You have " & Format(TotalCount, "#,##0") & " formulas."
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
Click to expand...
 
D

Dave Peterson

I think there's a slight bug in this.

If the worksheet doesn't have a formula, wscount retains its value from the
previous count.

Option Explicit

Sub CountFormulas()
Dim TotalCount As Long
Dim WSCount As Long
Dim WS As Worksheet
On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
WSCount = 0 '<-- added
WSCount = WS.UsedRange.SpecialCells(xlCellTypeFormulas).Count
TotalCount = TotalCount + WSCount
Next WS
MsgBox "You have " & Format(TotalCount, "#,##0") & " formulas."
End Sub
 
C

Chip Pearson

Dave,

You're right. WSCount needs to be reset to zero for each sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top